时间戳

首页 > 解决方案 > Selecting rows with maximum value in Oracle query

sql - Selecting rows with maximum value in Oracle query

问题描述

Let's say I've got this rows in my Oracle database:

N783AS          6 WA
N794SW          2 WA
N407SW          2 WI
N471CA         10 WI
N479CA          6 WI
N494CA          5 WI
N495CA          7 WI
N496CA         12 WI
N498CA          9 WI
N506CA          8 WI
N507CA          6 WI

What I'd like to obtain is this:

N496CA         12 WI
N783AS          6 WA

So, what I should do is, somehow, obtain, for each state (third column), the row with the maximum value of the second column. How can I do so?

标签： sqloracle

解决方案

最简单的方法是where子句中的相关子查询：

select t.*
from t
where t.col2 = (select max(t2.col2) from t t2 where t2.col3 = t.col3);

使用索引(col3, col2)可能是性能最高的解决方案，但它可以返回重复项。为避免这种情况，您可以使用row_number()：

select t.*
from (select t.*,
             row_number() over (partition by col3 order by col2 desc) as seqnum
      from t
     ) t
where seqnum = 1;

或者，这在某些情况下可能具有更好的性能：

select max(col1) keep (dense_rank first order by col2 desc), max(col2), col3
 from t
group by col3;

推荐阅读