FROM http://blog.csdn.net/jgmydsai/article/details/10574423
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
- SQL> SELECT NTH_VALUE(T.SNAME, 3) FROM FIRST OVER() AS 第三名,
- 2 NTH_VALUE(T.SNAME, 2) FROM LAST OVER() AS 倒数第二名,
- 3 T.*
- 4 FROM STUDENT T
- 5 ORDER BY AGE;
- 第三名 倒数第二名 SNO SNAME AGE
- ---------- ---------- ----- ---------- ----------
- KAY CHAD 6 JING 18
- KAY CHAD 4 MAGGIE 19
- KAY CHAD 8 KAY 20
- KAY CHAD 1 AARON 20
- KAY CHAD 3 DOUG 20
- KAY CHAD 9 GILLIAN 20
- KAY CHAD 7 BRIAN 21
- KAY CHAD 2 CHUCK 21
- KAY CHAD 10 CHAD 21
- KAY CHAD 5 STEVE 22
- 10 rows selected
- SQL>
注意,看例句中的重复记录,用NTH_VALUE只能取到其中一个