By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with data_view as (
select 1 key1, 1 key2, 1 tail1, 1 tail2, 1 tail3 from dual
union all
select 1, 1, 2, 2, 2 from dual
union all
select 1, 1, 3, 3, 3 from dual
union all
select 2, 2, 1, 2, 3 from dual
union all
select 2, 2, 3, 2, 1 from dual
union all
select 2, 2, 2, 2, 2 from dual
),
dub as (
select rownum rn,
key1, key2, tail1, tail2, tail3
from data_view
)
select * from dub a
where rn in (select max(rn) from dub b where a.key1 = b.key1 and a.key2 = b.key2)
| RN | KEY1 | KEY2 | TAIL1 | TAIL2 | TAIL3 |
|---|---|---|---|---|---|
| 6 | 2 | 2 | 2 | 2 | 2 |
| 3 | 1 | 1 | 3 | 3 | 3 |
with data_view as (
select 1 key1, 1 key2, 1 tail1, 1 tail2, 1 tail3 from dual
union all
select 1, 1, 2, 2, 2 from dual
union all
select 1, 1, 3, 3, 3 from dual
union all
select 2, 2, 1, 2, 3 from dual
union all
select 2, 2, 3, 2, 1 from dual
union all
select 2, 2, 2, 2, 2 from dual
),
dub as (
select row_number() over(partition by key1, key2 order by tail1) rn,
key1, key2, tail1, tail2, tail3
from data_view
)
select key1, key2, tail1, tail2, tail3 from dub a where rn = 1
| KEY1 | KEY2 | TAIL1 | TAIL2 | TAIL3 |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 1 | 2 | 3 |