Selecting rows with maximum value in Oracle query

Question

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?

Gordon Linoff · Accepted Answer · 2018-05-20 11:02:58Z

2

The simplest way is a correlated subquery in the where clause:

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

With an index on (col3, col2) this is probably the most performant solution, but it can return duplicates. To avoid that, you can use 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;

Or, this might have the better performance under some circumstances:

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

answered May 20, 2018 at 11:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Selecting rows with maximum value in Oracle query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related