Search code examples
sqloraclegroup-bymaxseq

GROUP BY ID and select MAX

Good Evening, I am working on a table like this in Oracle:

ID BALANCE SEQ
1 102 13
1 119 15
2 50 4
3 20 11
3 15 10
3 45 9
4 90 5
5 67 20
5 12 19
6 20 1

I want to select, for each ID, the BALANCE having MAX(SEQ).

So final result would be:

ID BALANCE SEQ
1 119 15
2 50 4
3 20 11
4 90 5
5 67 20
6 20 1

How can I do that? I've tried several Group by queries but with no success. Thanks for any help

Solution

  • One method is aggregation using keep:

    select id,
       max(balance) keep (dense_rank first order by seq desc) as balance,
       max(seq)
from t
group by id;