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 29 30 31 32 33
| with MaTable as
(
select 'nom1' as c1, 1 as c2, 'N' as c3, to_date('01/01/2000', 'dd/mm/yyyy') as c4 from dual union all
select 'nom1' , 3 , 'N' , to_date('01/01/2000', 'dd/mm/yyyy') from dual union all
select 'nom1' , 3 , 'Y' , to_date('31/12/1999', 'dd/mm/yyyy') from dual union all
select 'nom1' , 5 , 'Y' , to_date('01/01/2000', 'dd/mm/yyyy') from dual union all
select 'nom1' , 5 , 'N' , to_date('31/12/1999', 'dd/mm/yyyy') from dual union all
select 'nom1' , 7 , 'Y' , to_date('01/01/2010', 'dd/mm/yyyy') from dual union all
select 'nom1' , 7 , 'N' , to_date('31/12/2000', 'dd/mm/yyyy') from dual union all
select 'nom2' , 1 , 'Y' , to_date('01/01/2000', 'dd/mm/yyyy') from dual union all
select 'nom2' , 1 , 'N' , to_date('01/01/2000', 'dd/mm/yyyy') from dual
)
, sr1 as
(
SELECT c1, c2, c3, c4,
case
when max(c3) over(partition by c1, c2) = 'Y'
and max(c4) over(partition by c1, c2) = c4
then 1
end as c5
from MaTable
)
, sr2 as
(
select c1, c2, c3, c4,
row_number() over(partition by c1 order by c2 asc) as rn
from sr1
where c3 = 'Y'
and c5 = 1
)
select c1, c2, c3, c4
from sr2
where rn = 1; |
Partager