Styjun

I have a table such as the below:

|col1|col2|col3|col4|col5|
|----|----|----|----|----|
|A |1 |x |y |y |
|A |2 |x |x |y |
|B |1 |x |y |x |

What I want to do is return a single record for a non-unique value in col1, based on the maximum value in col2.

I could do this with a self inner join such as:

SELECT a.*
 FROM table a INNER JOIN (
 SELECT col1, 
 MAX(col2)
 FROM table
 GROUP BY col1) b 
 ON b.col1 = a.col1 AND b.col2 = a.col2

Which would return:

|col1|col2|col3|col4|col5|
|----|----|----|----|----|
|A |2 |x |x |y |
|B |1 |x |y |x |

However, this seems inefficient. Is there a more efficient way to do this?

I think you can prefer using in operator with a more direct way

select *
 from "table"
 where (col1,col2) in ( select col1,max(col2)
 from "table"
 group by col1 
 )

use correlated subquery

select * from tablename a
where col2 in (select max(col2) from tablename b where a.col1=b.col1)

You are using Oracle, do not need use correlated subquery or self-joins for this simple case. Just use analytic functions.

with s (col1, col2, col3, col4, col5) as (
select 'A', 1, 'x', 'y', 'y' from dual union all
select 'A', 2, 'x', 'x', 'y' from dual union all
select 'B', 1, 'x', 'y', 'x' from dual)
select*
from
 (select s.*, max(col2) over (partition by col1) mx
 from s
 )
where col2 = mx;

C COL2 C C C MX
- ---------- - - - ----------
A 2 x x y 2
B 1 x y x 1

Elapsed: 00:00:00.00

with s (col1, col2, col3, col4, col5) as (
select 'A', 1, 'x', 'y', 'y' from dual union all
select 'A', 2, 'x', 'x', 'y' from dual union all
select 'B', 1, 'x', 'y', 'x' from dual)
select*
from
 (select s.*, row_number() over (partition by col1 order by col2 desc) rn
 from s
 )
where rn = 1;

C COL2 C C C RN
- ---------- - - - ----------
A 2 x x y 1
B 1 x y x 1

Elapsed: 00:00:00.00

with s (col1, col2, col3, col4, col5) as (
select 'A', 1, 'x', 'y', 'y' from dual union all
select 'A', 2, 'x', 'x', 'y' from dual union all
select 'B', 1, 'x', 'y', 'x' from dual)
select
col1,
max(col2) col2,
max(col3) keep (dense_rank last order by col2) col3,
max(col4) keep (dense_rank last order by col2) col4,
max(col5) keep (dense_rank last order by col2) col5
from s
group by col1;

C COL2 C C C
- ---------- - - -
A 2 x x y
B 1 x y x

Elapsed: 00:00:00.01