1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| WITH ASG AS -- Vos données
(
SELECT 'ID000001' AS ID_RA, 'AA' as COL, date '2011-01-20' AS STAMP FROM dual union ALL
SELECT 'ID000001' , 'AB' , date '2010-12-21' FROM dual union ALL
SELECT 'ID000002' , 'BA' , date '2010-03-15' FROM dual union ALL
SELECT 'ID000003' , 'CA' , date '2010-07-08' FROM dual union ALL
SELECT 'ID000003' , 'CB' , date '2010-07-12' FROM dual union ALL
SELECT 'ID000003' , 'CC' , date '2010-08-11' FROM dual
)
, SR AS
(
SELECT id_ra, col,
row_number() over(partition BY id_ra ORDER BY stamp DESC) AS rn
FROM asg
)
SELECT id_ra, col
FROM SR
WHERE rn = 1;
ID_RA COL
-------- ---
ID000001 AA
ID000002 BA
ID000003 CC |
Partager