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 MaTable as
(
select 1 as idb, null as c1, 3 as c2, null as c3, to_date('03/05/2010', 'dd/mm/yyyy') as dt from dual union all
select 1 , 'toto' , 4 , null , to_date('04/05/2010', 'dd/mm/yyyy') from dual union all
select 1 , null , null , null , to_date('05/05/2010', 'dd/mm/yyyy') from dual union all
select 1 , null , 5 , null , to_date('06/05/2010', 'dd/mm/yyyy') from dual union all
select 1 , null , null , 2010 , to_date('10/05/2010', 'dd/mm/yyyy') from dual union all
select 2 , 'bobo' , null , 2009 , to_date('10/05/2010', 'dd/mm/yyyy') from dual union all
select 2 , null , 10 , null , to_date('10/05/2010', 'dd/mm/yyyy') from dual union all
select 2 , 'bobby' , 11 , null , to_date('10/05/2010', 'dd/mm/yyyy') from dual
)
, mt as
(
select idb,
last_value(c1 ignore nulls) over(partition by idb order by dt asc) as c1,
last_value(c2 ignore nulls) over(partition by idb order by dt asc) as c2,
last_value(c3 ignore nulls) over(partition by idb order by dt asc) as c3,
dt,
row_number() over(partition by idb order by dt desc) as rn
from MaTable
)
select idb, c1, c2, c3, dt
from mt
where rn = 1;
IDB C1 C2 C3 DT
1 toto 5 2010 10/05/2010
2 bobo 11 2009 10/05/2010 |
Partager