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
| SQL> WITH t AS ( SELECT 1 AS a, 1 AS b FROM dual
2 UNION ALL SELECT 1, 4 FROM dual
3 UNION ALL SELECT 1, 2 FROM dual)
4 SELECT 'LAST', a, b,
5 last_value(b) OVER (PARTITION BY a ORDER BY b) b_asc,
6 last_value(b) OVER (PARTITION BY a ORDER BY b desc) b_desc
7 FROM t
8 /
'LAS A B B_ASC B_DESC
---- ---------- ---------- ---------- ----------
LAST 1 1 1 1
LAST 1 2 2 2
LAST 1 4 4 4
SQL> WITH t AS ( SELECT 1 AS a, 1 AS b FROM dual
2 UNION ALL SELECT 1, 4 FROM dual
3 UNION ALL SELECT 1, 2 FROM dual)
4 SELECT 'LAST', a, b,
5 last_value(b) OVER (PARTITION BY a ORDER BY b DESC) b_desc,
6 last_value(b) OVER (PARTITION BY a ORDER BY b) b_asc
7 FROM t
8 /
'LAS A B B_DESC B_ASC
---- ---------- ---------- ---------- ----------
LAST 1 4 4 4
LAST 1 2 2 2
LAST 1 1 1 1 |
Partager