1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
with tmp as (SELECT 5003010 as num, 'A101' as code, to_date('15/02/2013', 'dd/mm/yyyy') as dte from dual union all
SELECT 5003010, 'A120', to_date('16/02/2013', 'dd/mm/yyyy') from dual union all
SELECT 5003010, 'A101', to_date('18/02/2013', 'dd/mm/yyyy') from dual union all
SELECT 5003010, 'A101', to_date('19/02/2013', 'dd/mm/yyyy') from dual union all
SELECT 5003010, 'A101', to_date('21/02/2013', 'dd/mm/yyyy') from dual union all
SELECT 5003010, 'A130', to_date('22/02/2013', 'dd/mm/yyyy') from dual union all
SELECT 5003010, 'A130', to_date('24/02/2013', 'dd/mm/yyyy') from dual union all
SELECT 5003010, 'A020', to_date('25/02/2013', 'dd/mm/yyyy') from dual union all
SELECT 5003010, 'A022', to_date('26/02/2013', 'dd/mm/yyyy') from dual),
tmp2 as (select n.*,
row_number() over(partition by num order by dte ) - row_number() over(partition by num, code order by dte) as sub1
from tmp n)
select num, code, min(dte)
from tmp2
group by num, code, sub1
order by 3 |
Partager