Is ROW_NUMBER() OVER () guaranteed to follow a recursive CTE's emission order?

(1) By Stephan Beal (stephan) on 2026-06-29 09:52:29 [source]

(Originally by anonymous poster and accidentally rejected... We need to add a confirmation to the Reject button.)

I'm walking an adjacency-list tree (pid -> id) with a recursive CTE and want each output row numbered in depth-first (pre-order) order. My question is specifically about the row numbering, not about getting the rows into the right order.

Schema and data:

CREATE TABLE items(id INTEGER PRIMARY KEY, pid INTEGER);
INSERT INTO items(id, pid) VALUES
  (1, NULL), (2, 1), (3, 2), (4, 2), (5, 1), (6, NULL);

Approach 1 - force depth-first emission with ORDER BY in the recursive term, then number with an empty window:

WITH RECURSIVE tree(id, pid, depth) AS (
    SELECT id, pid, 0
      FROM items
     WHERE pid IS NULL
  UNION ALL
    SELECT c.id, c.pid, t.depth + 1
      FROM items AS c
      JOIN tree  AS t ON c.pid = t.id
     ORDER BY 3 DESC          -- depth DESC -> depth-first emission
)
SELECT ROW_NUMBER() OVER () AS rn, id, pid
  FROM tree;

On my build this returns rn = 1..6 in exactly the depth-first order the CTE emits (1, 2, 3, 4, 5, 6).

Approach 2 - carry a sortable "path" column and order by it explicitly:

WITH RECURSIVE tree(id, pid, path) AS (
    SELECT id, pid, printf('%020d', id)
      FROM items
     WHERE pid IS NULL
  UNION ALL
    SELECT c.id, c.pid, t.path || printf('%020d', c.id)
      FROM items AS c
      JOIN tree  AS t ON c.pid = t.id
)
SELECT ROW_NUMBER() OVER (ORDER BY path) AS rn, id, pid
  FROM tree
 ORDER BY path;

Question: In Approach 1, is ROW_NUMBER() OVER () guaranteed to assign its numbers in the order the recursive CTE produces rows - i.e. the depth-first order I forced with ORDER BY ... DESC in the recursive SELECT? Or is that numbering order unspecified, and only working by chance in the current implementation?

I'm unsure because the window-function docs say row_number() numbers by the ORDER BY in the window definition, and an empty OVER () has none. Meanwhile the WITH docs note that the recursive queue's extraction order is undefined without ORDER BY (here I do supply one in the recursive SELECT, but I don't know whether that has any defined relationship to the order ROW_NUMBER() OVER () then sees).

In short: can I rely on Approach 1 for a depth-first row number, or do I need the path column (Approach 2) to make it guaranteed?

Thanks

(2) By Lifepillar (lifepillar) on 2026-06-29 12:26:41 in reply to 1 [link] [source]

Others surely know better, but apparently ORDER BY… DESC inside the CTE outputs rows in depth-first order (actually, pre-order), and the main SELECT seems to be guaranteed to read them in that order, if we trust the example in the manual (and we do). So, using ROW_NUMBER() OVER () to number them should be just fine.

Note that other systems may not support that (quite odd, to me) syntax, in case you need portability.

(3) By anonymous on 2026-06-30 09:01:01 in reply to 2 [link] [source]

I guess that too, but I could not find any detail about not having and ORDER in OVER. Any I wanted to make sure it is not an implemented detail.

(4) By anonymous on 2026-06-30 18:20:26 in reply to 1 [link] [source]

This is not intended to be an answer; for I'm sure you know this and more than I do. Perhaps it sort of lumps it into a similar category that may or may not be documented somewhere.

All the aggregate functions have this clause applied:

If no ORDER BY clause is specified, the inputs to the aggregate occur in an arbitrary order that might change from one invocation to the next.

However, before the ORDER BY was added to the aggregate functions, we relied on first sorting the table upon which the aggregate was to be applied. That must still be true. Is that documented any where or just understood as standard database/SQL?

I realize that row_number() is not an aggregate function but it appears it works the same in this respect. I sure hope so because I've been relying on it for some time now; that is, relying on a "pre-sort" as in the examples below.

All are as expected (by me, anyway) except the next to the last. I thought perhaps the row_number() over() would be the table order (as in the first row_number query) and the row_number() over( order by id) would be ordered. But they are both the same. Of course, no one would ever do or rely on that.

drop table data;
create table data(id);
insert into data values (3),(2),(1);
delete from data where id=2;
insert into data values (8);
update data set id=5 where id=3;

select 
   group_concat(id),
   json_group_array(id)
from data
;
/*
╭──────────────────┬──────────────────────╮
│ group_concat(id) │ json_group_array(id) │
╞══════════════════╪══════════════════════╡
│ 5,1,8            │ [5,1,8]              │
╰──────────────────┴──────────────────────╯
*/
with 
data_ordered as (
select * from data order by id asc
)
select 
   group_concat(id),
   json_group_array(id)
from data_ordered
;
/*
╭──────────────────┬──────────────────────╮
│ group_concat(id) │ json_group_array(id) │
╞══════════════════╪══════════════════════╡
│ 1,5,8            │ [1,5,8]              │
╰──────────────────┴──────────────────────╯
*/
select 
   id, 
   row_number() over ()
from data
;
/*
╭────┬──────────────────────╮
│ id │ row_number() over () │
╞════╪══════════════════════╡
│  5 │                    1 │
│  1 │                    2 │
│  8 │                    3 │
╰────┴──────────────────────╯
*/
with 
data_ordered as (
select * from data order by id asc
)
select 
   id, 
   row_number() over ()
from data_ordered
;
/*
╭────┬──────────────────────╮
│ id │ row_number() over () │
╞════╪══════════════════════╡
│  1 │                    1 │
│  5 │                    2 │
│  8 │                    3 │
╰────┴──────────────────────╯
*/
select 
   id, 
   row_number() over (),
   row_number() over (order by id asc)
from data
;
/*
╭────┬──────────────────────┬──────────────────────╮
│ id │ row_number() over () │ row_number() over... │
╞════╪══════════════════════╪══════════════════════╡
│  1 │                    1 │                    1 │
│  5 │                    2 │                    2 │
│  8 │                    3 │                    3 │
╰────┴──────────────────────┴──────────────────────╯
*/
select 
   id, 
   row_number() over (order by id desc),
   row_number() over (order by id asc)
from data
;
/*
╭────┬──────────────────────┬──────────────────────╮
│ id │ row_number() over... │ row_number() over... │
╞════╪══════════════════════╪══════════════════════╡
│  8 │                    1 │                    3 │
│  5 │                    2 │                    2 │
│  1 │                    3 │                    1 │
╰────┴──────────────────────┴──────────────────────╯
*/

(5) By anonymous on 2026-07-03 15:42:07 in reply to 1 [link] [source]

I am the poster, can I get a confirmation about this question from someone of the team?