CODEWITHSUNDEEP
sqloracle-database
Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

Get latest record after ordering columns based on sequence

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

enter image description here

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

Upvotes: 1

Views: 136

Answers (3)

Popeye
Popeye

Reputation: 35920

I would suggest doing self outer join and take the record where it is not able to find any record CURR_BAL has any record with the same PREV_BAL.

I don't know if it will help you or not, but It is giving the desired answer

Something like the following:

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
 )
 -- Your query starts from here
 SELECT
      ID_DATE, PREV_BAL, CURR_BAL
 FROM
     (
          SELECT
              D.*, P.ID_DATE   AS P_ID_DATE
          FROM
              DA D LEFT JOIN DA P ON D.CURR_BAL = P.PREV_BAL
      )
 WHERE P_ID_DATE IS NULL;

Result

        ID_DATE   PREV_BAL   CURR_BAL
     ---------- ---------- ----------
       20200201        192        195

Cheers!!

Upvotes: 1

gwatene
gwatene

Reputation: 81

This was a simple question just use the query below

SELECT * FROM ( 
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 ORDER BY prev_bal DESC  )
WHERE rownum=1 ORDER BY curr_bal DESC

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

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)

dbfiddle

Upvotes: 2

Related Questions