1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
/* Formatted on 29/10/2010 15:07:51 (QP5 v5.163.1008.3004) */
WITH matable
AS (SELECT 5 AS eid, 4 AS ecd, TO_DATE ('24/10/2010', 'dd/mm/yyyy') AS edt FROM DUAL
UNION ALL
SELECT 5, 19, TO_DATE ('25/10/2010', 'dd/mm/yyyy') FROM DUAL
UNION ALL
SELECT 5, 13, TO_DATE ('26/10/2010', 'dd/mm/yyyy') FROM DUAL
UNION ALL
SELECT 5, 84, TO_DATE ('27/10/2010', 'dd/mm/yyyy') FROM DUAL
UNION ALL
SELECT 5, 9, TO_DATE ('28/10/2010', 'dd/mm/yyyy') FROM DUAL
UNION ALL
SELECT 6, 19, SYSDATE - 1 FROM DUAL
UNION ALL
SELECT 6, 86, SYSDATE FROM DUAL)
SELECT *
FROM (SELECT eid,
edt,
ecd,
LEAD (ecd) OVER (PARTITION BY eid ORDER BY edt DESC) AS ecd_prev
FROM matable
)
WHERE ecd_prev = 19 |
Partager