1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| With MaTable as
(
select 12 as age, 'LENS' as ville, 62 as region, 'Jim' as nom, 'Jam' as prenom, 'H' as sexe from dual union all
select 13 , 'LENS' , 62 , 'Jim' , 'Jam' , 'H' from dual union all
select null , 'LENS' , 62 , 'John' , 'Doe' , 'H' from dual union all
select 13 , 'LENS' , 62 , 'Jane' , 'Maj' , 'F' from dual
)
, sr as
(
select age, ville, region, nom, prenom, sexe
, row_number() over(partition by ville, region order by age desc nulls last, sexe desc) as rn
from MaTable
)
select age, ville, region, nom, prenom, sexe
from sr
where rn = 1;
AGE VILLE REGION NOM PRENOM SEXE
---- ----- ------ ---- ------ ----
13 LENS 62 Jim Jam H |
Partager