CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
David Hanul Lee
David Hanul Lee

Reputation: 3

Oracle PLSQL - Selecting Row with Max Value

I have rows like this:

( a , #$@$ , $$ , 3 )
( c , ###$ , ## , 0 )
( a , #@$# , !! , 2 )
( b , #@## , $$ , 0 )

If I want to get the result like below

( a , #$@$ , $$ , 3 )
( c , ###$ , ## , 0 )
( b , #@## , $$ , 0 )

Which is based on grouping by column 1 and choose the rows with max value in column 4 independent of other columns (2 & 3).

Instead of creating subquery, is there a way to do this?

Upvotes: 0

Views: 296

Answers (2)

Mahamoutou
Mahamoutou

Reputation: 1555

Without using subquery, you can use keep dense_rank function (its aggregate version) like below :

with your_table (col1, col2, col3, col4) as (
select 'a', '#$@$' , '$$' , 3 from dual union all
select 'c', '###$' , '##' , 0 from dual union all
select 'a', '#@$#' , '!!' , 2 from dual union all
select 'b', '#@##' , '$$' , 0 from dual
)
select col1
, max(col2)keep(dense_rank first order by col4 desc)col2
, max(col3)keep(dense_rank first order by col4 desc)col3
, max(col4)keep(dense_rank first order by col4 desc)col4
from your_table t
group by col1
;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142710

Why without a subquery? They are designed for such a purpose.

Current table contents:

SQL> select * from test order by col1, col4;

COL1  COL2  COL3        COL4
----- ----- ----- ----------
a     #@$#  !!             2
a     #$@$  $$             3
b     #@$$  $$             0
c     ###$  ##             0

Using analytic function, partition rows by col1 and order them by col4 in descending order; then fetch the "first" row per each group (partition).

SQL> select col1, col2, col3, col4
  2  from (select col1, col2, col3, col4,
  3          row_number() over (partition by col1 order by col4 desc) rn
  4        from test
  5       )
  6  where rn = 1
  7  order by col1;

COL1  COL2  COL3        COL4
----- ----- ----- ----------
a     #$@$  $$             3
b     #@$$  $$             0
c     ###$  ##             0

SQL>

Upvotes: 0

Related Questions