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