8

I want to get the values of a column based on max of a different column grouped by another column.

I have this table:

KEY NUM VAL
A   1   AB
B   1   CD
B   2   EF
C   2   GH
C   3   HI
D   1   JK
D   3   LM

And want this result:

KEY VAL
A   AB
B   EF
C   HI
D   LM

I could actually use this query to get it.

select KEY, VAL
from TABLE_NAME TN
where NUM = (
    select max(NUM)
    from TABLE_NAME TMP
    where TMP.KEY = TN.KEY
    )

However is there a more elegant way in ORACLE SQL (10g or above) to get the result?

Reason behind it is that there are actually multiple KEYs and it just looks a bit ugly.

DRTauli
  • 731
  • 1
  • 8
  • 25
  • just for people searching for the samish thing... and especially when you cannot use nested queries, (because of https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:1853075500346799932), you can take a look here: https://stackoverflow.com/questions/65866812/explanation-of-keep-in-oracle-first-last – hello_earth Oct 01 '21 at 18:08

1 Answers1

13

You can approach this using row_number():

select key, val
from (select t.*, row_number() over (partition by key order by num desc) as seqnum
      from table_name t
     ) t
where seqnum = 1;

Whether you consider this more "elegant" is probably a matter of taste.

I should point out that this is subtly different from your query. This is guaranteed to return one row for each key; yours could return multiple rows. If you want that behavior, just use rank() or dense_rank() instead of row_number().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • You are god! This is exactly what I was looking for. The ability to simply "filter" one table without any joins to the min or max values. Amazing! – kiradotee Mar 07 '18 at 15:45