Oracle - Custom Sort-CodePudding

I have a dataset

**ITEM              ITEM_PARENT** 
100096635          (null)
100201836          (null)
1234560690640      100096635
1234561457648      100201836

I want to sort as:

**ITEM              ITEM_PARENT** 
100096635          (null)
1234560690640      100096635
100201836          (null)
1234561457648      100201836

How can I do that?

CodePudding user response：

You are looking for hierarchical order. Classic application of hierarchical query:

TEST DATA

create table tbl (item number, item_parent number);

insert into tbl (item, item_parent)
  select 100096635,     null      from dual union all
  select 100201836,     null      from dual union all
  select 1234560690640, 100096635 from dual union all
  select 1234561457648, 100201836 from dual
;

QUERY AND OUTPUT

select  item, item_parent
from    tbl
start   with item_parent is null
connect by item_parent = prior item
order   siblings by item
;


           ITEM ITEM_PARENT
--------------- -----------
      100096635            
  1234560690640   100096635
      100201836            
  1234561457648   100201836

Most of the work is done by the connect by thing; order siblings by only determines in what order the "roots" should be considered (the rows where the parent is null), and in the case of "sibling" rows, how those should be ordered. The "hierarchical" ordering is done by the hierarchical query regardless of what you choose to order siblings by.

CodePudding user response：

You can sort by using

ORDER BY NVL(item_parent,item), item_parent NULLS FIRST

Demo