By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH positions(id,time,col1,col2) AS
(
SELECT 1,1,11,17 FROM dual UNION ALL
SELECT 1,2,12,18 FROM dual UNION ALL
SELECT 3,1,13,19 FROM dual UNION ALL
SELECT 1,3,14,20 FROM dual UNION ALL
SELECT 2,1,15,21 FROM dual UNION ALL
SELECT 3,2,16,22 FROM dual
)
SELECT MAX(time) KEEP (DENSE_RANK LAST ORDER BY time)
OVER (PARTITION BY id) AS time_max,
p.*
FROM positions p
ORDER BY id
| TIME_MAX | ID | TIME | COL1 | COL2 |
|---|---|---|---|---|
| 3 | 1 | 2 | 12 | 18 |
| 3 | 1 | 1 | 11 | 17 |
| 3 | 1 | 3 | 14 | 20 |
| 1 | 2 | 1 | 15 | 21 |
| 2 | 3 | 2 | 16 | 22 |
| 2 | 3 | 1 | 13 | 19 |