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?

Answer

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
;