I would like to order my table using two columns in manner of sequence as described:
The current balance to start as the previous balance in next row.
Below is my data
ID_DATE PREV_BAL CURR_BAL 20200201, 157, 192 20200201, 192, 195 20200201, 123, 124 20200201, 124, 157 20200201, 125, 123
And i want it to be ordered first in below sequence
Then select the top most row as my final result i.e.
ID_DATE PREV_BAL CURR_BAL 20200201, 192, 195
Any assistance
with da as (
select 20200201 id_date, 157 prev_bal, 192 curr_bal from dual
union all select 20200201 id_date, 192 prev_bal, 195 curr_bal from dual
union all select 20200201 id_date, 123 prev_bal, 124 curr_bal from dual
union all select 20200201 id_date, 124 prev_bal, 157 curr_bal from dual
union all select 20200201 id_date, 125 prev_bal, 123 curr_bal from dual
)
SELECT * FROM da
Advertisement
Answer
This is a hierarchy, you can see the whole cycle running this query:
select da.*, sys_connect_by_path(curr_bal, ' - ') path from da connect by prior prev_bal = curr_bal start with not exists (select 1 from da t where t.prev_bal = da.curr_bal) ID_DATE PREV_BAL CURR_BAL PATH -------- ---------- ---------- ------------------------------- 20200201 192 195 - 195 20200201 157 192 - 195 - 192 20200201 124 157 - 195 - 192 - 157 20200201 123 124 - 195 - 192 - 157 - 124 20200201 125 123 - 195 - 192 - 157 - 124 - 123
But if you want only most parent row just use Tejash’s query or not in or not exists:
select * from da where not exists (select 1 from da t where t.prev_bal = da.curr_bal)
If you want to go in other direction through all tree, as described in your question, change connect by clause and take leaf row(s):
select da.*, sys_connect_by_path(curr_bal, ' - ') path from da where connect_by_isleaf = 1 connect by prev_bal = prior curr_bal start with not exists (select 1 from da t where da.prev_bal = t.curr_bal)