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
|
SQL> WITH agents_contrats AS
2 (SELECT 'tata' nom, 'tutu' prenom, '0548' matricule, '01' nocontrat
3 FROM DUAL
4 UNION ALL
5 SELECT 'toto', 'titi', '0123', '01' nocontrat
6 FROM DUAL
7 UNION ALL
8 SELECT 'toto', 'titi', '0125', '02' nocontrat
9 FROM DUAL
10 UNION ALL
11 SELECT 'toto', 'titi', '0124', '03' nocontrat
12 FROM DUAL
13 UNION ALL
14 SELECT 'tete', 'mumu', '0587', '01' nocontrat
15 FROM DUAL)
16 SELECT nom, prenom,
17 MAX (matricule)KEEP (DENSE_RANK LAST ORDER BY nocontrat) matricule,
18 MAX (nocontrat)KEEP (DENSE_RANK LAST ORDER BY nocontrat) nocontrat
19 FROM agents_contrats
20 GROUP BY nom, prenom;
NOM PREN MATR NO
---- ---- ---- --
tata tutu 0548 01
tete mumu 0587 01
toto titi 0124 03
SQL> |
Partager