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 38 39 40 41 42 43 44 45 46 47
|
WITH tab AS
(SELECT 1 code, 30 num, 'Nom1' nom
FROM DUAL
UNION ALL
SELECT 1, 1, 'Nom2'
FROM DUAL
UNION ALL
SELECT 1, 2, 'Nom3'
FROM DUAL
UNION ALL
SELECT 1, 3, 'Nom4'
FROM DUAL
UNION ALL
SELECT 1, 30, 'Nom5'
FROM DUAL
UNION ALL
SELECT 1, 10, 'Nom6'
FROM DUAL
UNION ALL
SELECT 2 code, 30 num, 'Nom10' nom
FROM DUAL
UNION ALL
SELECT 2, 10, 'Nom11'
FROM DUAL
UNION ALL
SELECT 2, 40, 'Nom12'
FROM DUAL
UNION ALL
SELECT 2, 50, 'Nom13'
FROM DUAL)
SELECT code, num, nom
FROM (SELECT tab.*,
DENSE_RANK () OVER (PARTITION BY code ORDER BY num DESC) rn
FROM tab) tab
WHERE rn = 1 OR num = 10;
CODE NUM NOM
---------- ---------- -----
1 30 Nom1
1 30 Nom5
1 10 Nom6
2 50 Nom13
2 10 Nom11
5 rows selected. |
Partager