I have a table that looks like below:

[Col A  Col B
000000  null
000924  000244
000244  000000
000758  000000][1]

I want to be able to query and get the output sorted in such a way that, ColA displays values beneath the value specified in Col B. Example: '000758' and '000244' should appear below '000000' and '000924' should appear below '000244'. Please see below for expected output:

Expected Result:

[Col A  
000000
000758
000244  
000924][1]  

CodePudding user response：

Use a hierarchical query with ORDER SIBLINGS BY ...:

SELECT colA
FROM   table_name
START WITH colb IS NULL
CONNECT BY PRIOR colA = colB
ORDER SIBLINGS BY colA DESC

Which, for the sample data:

CREATE TABLE table_name (ColA, ColB) AS
SELECT '000000', null     FROM DUAL UNION ALL
SELECT '000924', '000244' FROM DUAL UNION ALL
SELECT '000244', '000000' FROM DUAL UNION ALL
SELECT '000758', '000000' FROM DUAL;

Outputs:

COLA
000000
000758
000244
000924

db<>fiddle here