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?
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; 