How do I ORDER BY timestamp in this recursive query?

Question

I have a working reqursive query for my comments table.

WITH RECURSIVE comments_tree(
 id,
 content,
 created_at,
 path,
 level
) AS (
 SELECT
 id,
 content,
 created_at,
 ARRAY[id],
 1 as level
 FROM comments
 WHERE
 parent_id IS NULL
UNION ALL
 SELECT
 comments.id,
 comments.content,
 comments.created_at,
 path || comments.id,
 comments_tree.level + 1 as level
 FROM comments_tree
 JOIN comments ON comments_tree.id = comments.parent_id
 WHERE NOT comments.id = ANY(path)
)
SELECT * FROM comments_tree ORDER BY path;

I have added 5 comments (2 of them have content 'parent x', others - 'child x').

Example of output (formatted to be more readable):

"-> parent 1: 16:49"
"--> child 1: 16:50"
"--> child 2: 16:51"
"--> child 3: 16:52"
"-> parent 2: 16:53"

I want to order comments by their creation time (created_at field) like this:

"-> parent 2: 16:53"
"-> parent 1: 16:49"
"--> child 3: 16:52"
"--> child 2: 16:51"
"--> child 1: 16:50"

How do I do that?

• joanolo: (1) Aren't your examples exchanged? The first one is the one ordered by creation time. (2) Have you just tried changing the last line of your query: ORDER BY created_at ?
• blackst0ne: Yes, I have. Nothing was changed.
• Erwin Brandstetter: Basic requirement for this kind of questions: table definition showing relevant columns with data types and all constraints (CREATE TABLE statements). Most importantly: is created_at UNIQUE NOT NULL? Else it gets more complex. And your version of Postgres. Also: Are you trying to solve this for only 2 levels or for any number of levels? Sort by root timestamp of level, then by timestamp of each parent level recursively? Please edit to clarify.

Answer 1

For simplicity I omit additional columns to focus on core of the problem.

Simple solution

Collect timestamps (created_at) in another array (path_created) like:

WITH RECURSIVE tree AS (
 SELECT id, created_at, ARRAY[id] AS path
 , ARRAY[created_at] AS path_created
 FROM comments
 WHERE parent_id IS NULL
 UNION ALL
 SELECT c.id, c.created_at, path || c.id
 , t.path_created || c.created_at
 FROM tree t
 JOIN comments c ON t.id = c.parent_id
 WHERE NOT c.id = ANY(t.path)
 )
SELECT *
FROM tree
ORDER BY path_created[1] DESC, path[1] ASC
 , path_created[2] DESC, path[2] ASC
 , path_created[3] DESC, path[3] ASC
 , ...

If two threads start at the same created_at, sort the smaller id first.

... which is good enough for a low maximum number of elements. Works if there are fewer elements in the array. It's neither very dynamic nor very elegant for an unknown number of levels, though.

Dynamic number of levels

You could concatenate or calculate a single value from created_at and id or use a document type like jsonb. All of these solutions have corner cases. If you put some smarts into it, it'll be unambiguous.

The general, clean solution is to compute the row number for each row per level with row_number(), ordered by any number of columns or expressions directly:

WITH RECURSIVE tree AS (
 SELECT id, created_at, ARRAY[id] AS path
 , ARRAY[row_number() OVER (ORDER BY created_at DESC, id ASC)] AS path_sort
 FROM comments
 WHERE parent_id IS NULL
 UNION ALL
 SELECT c.id, c.created_at, t.path || c.id
 , t.path_sort || row_number() OVER (ORDER BY c.created_at DESC, c.id ASC)
 FROM tree t
 JOIN comments c ON t.id = c.parent_id
 WHERE NOT c.id = ANY(path)
 )
SELECT *
FROM tree
ORDER BY path_sort;

dbfiddle here

row_number() breaks ties among peers in any case. In the example the added id to the ORDER BY clause of the window definition. Assuming it is the PK and therefore breaks ties in deterministic fashion.

• blackst0ne: Thank you for the idea! I changed your code a bit. Everythin works like a charm!