根据由另一列分组的不同列的最大值获取值 [英] Get value based on max of a different column grouped by another column

问题描述

我想根据由另一列分组的不同列的最大值来获取列的值.

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

我有这张桌子:

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

想要这个结果:

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
    )

但是在 ORACLE SQL(10g 或以上)中有更优雅的方式来获得结果吗?

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

背后的原因是实际上有多个KEY，只是看起来有点难看.

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

推荐答案

您可以使用 row_number() 来解决这个问题:

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.

我应该指出，这与您的查询略有不同.这保证为每个 key 返回一行；你的可以返回多行.如果您想要这种行为，只需使用 rank() 或 dense_rank() 而不是 row_number().

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().

这篇关于根据由另一列分组的不同列的最大值获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！