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
|
WITH MaTable AS
(SELECT 'A' AS c1, 1 AS c2, '' AS c3 FROM dual union ALL
SELECT 'B' , 2 , '' FROM dual union ALL
SELECT 'C' , 1 , 'R' FROM dual union ALL
SELECT 'D' , 4 , 'R' FROM dual union ALL
SELECT 'Z' , 8 , 'M' FROM dual union ALL
SELECT 'E' , 5 , '' FROM dual union ALL
SELECT 'F' , 6 , 'R' FROM dual
)
SELECT c1, c2, c3
FROM (SELECT c1, c2, c3,
MAX (CASE
WHEN c2 = 1
THEN 1
ELSE 0
END) OVER (PARTITION BY c3) ind
FROM matable m
WHERE c2 = 1 OR c3 IS NOT NULL)
WHERE ind = 1
C1 C2 C3
- ---------- -
F 6 R
C 1 R
D 4 R
A 1
4 rows selected. |
Partager