I have this query:
WITH RECURSIVE cte(Id, Type, ParentId, CreationDate, SenderId, Message) AS
(
SELECT c.* FROM (SELECT Id, Type, ParentId, CreationDate, SenderId, Message
FROM GroupMessage
WHERE GroupId = '4a80590e-036c-45b6-a512-be0d8e48fbb6'
AND ParentId is NULL
ORDER BY CreationDate DESC
LIMIT 20 OFFSET 0) as c
UNION
SELECT c.Id, cte.Type+1, c.ParentId, c.CreationDate, c.SenderId, c.Message
FROM cte, GroupMessage c
WHERE c.ParentId = cte.Id AND c.Type = '1'
ORDER BY CreationDate DESC
)
SELECT * FROM cte
Currently I am limiting the number of parent messages to 20 most recent ones. How could I also limit the number of children to lets say 5 or 10 most recent ones? I Tried adding a LIMIT keyword after the UNION ORDER BY, but then the whole query returned only 5 or 10 rows. Also the OPTION keyword is not available in Sqlite3 and I am running out of ideas.
Aucun commentaire:
Enregistrer un commentaire