1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| With ta as
(
select 1 as id, 'Nom1' as nom from dual union all
select 2 , 'Nom2' from dual union all
select 3 , 'Nom3' from dual
)
, tb as
(
select 1 as id, 'Ville1' as ville, trunc(sysdate) as dt from dual union all
select 2 , 'Ville2' , trunc(sysdate) from dual union all
select 2 , 'Ville1' , trunc(sysdate) - 1 from dual
)
, sr as
(
select ta.nom, tb.ville, tb.dt,
row_number() over(partition by ta.id order by tb.dt desc) as rn
from ta
left outer join tb
on tb.id = ta.id
)
select nom, ville, dt
from sr
where rn = 1;
NOM VILLE DT
Nom1 Ville1 29/03/2010
Nom2 Ville2 29/03/2010
Nom3 |
Partager