CODEWITHSUNDEEP
sqloracle-database
Elavarasan
Elavarasan

Reputation: 301

Need SQL to Get Max of row based on my specific column while joining

Hi I have sample Oracle query and its respective output

select t1.account,t1.ID ,t2.NAME , t2.CHILDAC from t1,t2 where t1.ID = t2.ID;

Output Example

ACCOUNT       ID        NAME    CHILDAC
9001          1         xxx      root1
9006          3         xxy      root1
9003          4         xra      root1
9008          5         xii      root2
9011          6         xxt      root2
9045          7         xxy      root3
9089          8         xxu      root3
9033          10        xss      root4

My expected output is given below,which means I need to return the row of unique value of CHILDAC column with max of ID column

Expected out

ACCOUNT       ID        NAME    CHILDAC
9003          4         xra      root1
9011          6         xxt      root2
9089          8         xxu      root3
9033          10        xss      root4

Please provide me SQL for the above mentioned scenoria

Upvotes: 0

Views: 35

Answers (1)

sgeddes
sgeddes

Reputation: 62831

You can use row_number() with a subquery for this:

select *
from (
    select t1.account, t1.ID, t2.NAME, t2.CHILDAC, 
           row_number() over (partition by t2.Childac order by t1.id desc) rn
    from t1 
        join t2 on t1.ID = t2.ID
) t
where rn = 1

Upvotes: 1

Related Questions