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
| with T1(nom, datedeb, datefin, crit) as
(SELECT 'A', '28-05-2015', '29-09-2021', 'DGL1' union all
SELECT 'B', '06-05-2015', '26-10-2021', 'DGL1' union all
SELECT 'C', '28-05-2015', '21-11-2019', 'DGL1' union all
SELECT 'C', '28-05-2015', '21-11-2019', 'DGL2' union all
SELECT 'D', '15-04-2015', '04-10-2021', 'DGL1' union all
SELECT 'D', '22-12-2017', '05-01-2022', 'DGL2' union all
SELECT 'D', '06-01-2017', '21-07-2022', 'DGL3' union all
SELECT 'E', '28-05-2015', '29-04-2020', 'DGL1' union all
SELECT 'E', '28-05-2015', '29-04-2020', 'DGL2' union all
SELECT 'E', '30-06-2015', '29-04-2020', 'DGL3' union all
SELECT 'F', '28-05-2015', '04-10-2021', 'DGL1' union all
SELECT 'F', '22-12-2017', '01-07-2023', 'DGL2' union all
SELECT 'G', '28-05-2015', '29-11-2020', 'DGL1' union all
SELECT 'G', '28-05-2015', '04-10-2021', 'DGL2' union all
SELECT 'H', '28-05-2015', '12-04-2023', 'DGL1'
)
select nom
, datedeb
, datefin
, crit
from T1 main
where not exists
(select 1
from T1 subq
where subq.nom = main.nom
and subq.crit>main.crit
)
order by nom |
Partager