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 34 35 36 37
| With MaTable (Prenom, Dateval, Lieu) AS
(
select 'Jean' , convert(datetime, '01.01.1999', 104), 'Paris' union all
select 'Jean' , convert(datetime, '01.04.2000', 104), 'Lyon' union all
select 'Chris', convert(datetime, '01.05.2000', 104), 'Marseille' union all
select 'Jean' , convert(datetime, '01.06.2002', 104), 'La Rochelle' union all
select 'Chris', convert(datetime, '01.07.2002', 104), 'Lyon' union all
select 'Jean' , convert(datetime, '02.08.2003', 104), 'Lyon' union all
select 'Jean' , convert(datetime, '04.06.2005', 104), 'Marseille' union all
select 'Jean' , convert(datetime, '05.09.2006', 104), 'Paris' union all
select 'Jean' , convert(datetime, '06.08.2007', 104), 'Paris' union all
select 'Jean' , convert(datetime, '07.02.2008', 104), 'Paris' union all
select 'Jean' , convert(datetime, '10.03.2009', 104), 'Lyon'
)
, SR (Prenom, Dateval, Lieu, grp_id) AS
(
select Prenom, Dateval, Lieu,
row_number() over(partition by Prenom order by Dateval asc) -
row_number() over(partition by Prenom, Lieu order by Dateval asc) as grp_id
from MaTable
)
select Prenom, min(Dateval) as Dateval, Lieu
from SR
group by Prenom, Lieu, grp_id
order by Dateval asc;
Prenom Dateval Lieu
------ ------------ -----------
Jean 01.01.1999 Paris
Jean 01.04.2000 Lyon
Chris 01.05.2000 Marseille
Jean 01.06.2002 La Rochelle
Chris 01.07.2002 Lyon
Jean 02.08.2003 Lyon
Jean 04.06.2005 Marseille
Jean 05.09.2006 Paris
Jean 10.03.2009 Lyon |
Partager