CODEWITHSUNDEEP
sqloracle-databasehierarchyrow-number
Pham X. Bach
Pham X. Bach

Reputation: 5442

Oracle_ ROW_NUMBER for paging hierarchy query

This time I face a problem that is paging query for a hierarchy table.

table_name(id varchar2(50), id_parent varchar2(50) );

So for sample data I have:

WITH table_name AS
(
    SELECT '3' id, '' id_parent FROM DUAL UNION ALL
    SELECT '5' id, '3' id_parent FROM DUAL UNION ALL
    SELECT 's' id, '3' id_parent FROM DUAL UNION ALL
    SELECT '4' id, 'as' id_parent FROM DUAL UNION ALL
    SELECT 'aa' id, 'as' id_parent FROM DUAL UNION ALL
    SELECT 'as' id, '3' id_parent FROM DUAL UNION ALL
    SELECT 'ad' id, '3' id_parent FROM DUAL UNION ALL
    SELECT '2' id, '' id_parent FROM DUAL 
)
SELECT LPAD('-', 2 * (level - 1), '-') || id AS id1,
    id_parent, 
    rownum  --Seem not legit here
FROM table_name
START WITH id_parent IS NULL
CONNECT BY PRIOR id = id_parent 
--ORDER SIBLINGS BY id
;

This is expected output:

id          id_parent   rownum
2                       1
3                       2
--5         3           3
--ad        3           4
--as        3           5
----4       as          6
----aa      as          7
--s         3           8

For now I count on rownum for row_number to make paging. As Oracle hierarchy query's doc, this make sure I can display the tree as pre-order Traveral that's what we want, but as SQL in general, IMO it don't guarantee that the same-level-nodes will be sorted (or not?).

So I need something like ROW_NUMBER() OVER (ORDER SIBLINGS BY id). But I have got no luck searching for something like that.

Is there some way to work with this?

Upvotes: 0

Views: 1380

Answers (1)

MT0
MT0

Reputation: 168311

Order first and then generate the ROWNUM afterwards using a sub-query:

SELECT t.*,
       ROWNUM
FROM   (
  SELECT LPAD('-', 2 * (level - 1), '-') || id AS id1,
      id_parent
  FROM table_name
  START WITH id_parent IS NULL
  CONNECT BY PRIOR id = id_parent 
  ORDER SIBLINGS BY id
) t;

You need the outer query as the order in which execution happens in a query is:

  • The rows are selected;
  • Then the WHERE clause filters are applied (and the ROWNUM pseudo-column is generated for each row that matches all the WHERE clause filters - in this case there is no WHERE clause so all rows will be numbered);
  • Then the ORDER BY clause is applied.

Applying this in a single query will get the rows found in the database then give them a row number and finally order those rows (not what you want). Using an inner query you can force the ORDER BY clause to be applied first and then the generation of the row numbers will occur, subsequently, in the execution of the outer query.

Upvotes: 1

Related Questions