Matteo Beccati <p...@beccati.com> writes: > WITH RECURSIVE t (mailbox, uid, date, subject, sender, has_attachments, > parent_uid, idx, depth) AS ( > SELECT mailbox, uid, date, subject, sender, has_attachments, parent_uid, > uid::text, 1 > FROM arc_messages > WHERE parent_uid IS NULL AND mailbox = 15 > UNION ALL > SELECT a.mailbox, a.uid, a.date, a.subject, a.sender, a.has_attachments, > a.parent_uid, t.idx || '.' || a.uid::text, t.depth + 1 > FROM t JOIN arc_messages a USING (mailbox) > WHERE t.uid = a.parent_uid > ) SELECT * FROM t ORDER BY idx > > Any improvements to sorting are welcome :)
What I'd like would be to have it sorted by activity, showing first the thread which received the later messages. I'm yet to play with CTE and window function myself so without a database example to play with I won't come up with a nice query, but I guess a more educated reader will solve this without a sweat, as it looks easier than sudoku-solving, which has been done already :) Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers