NTH_VALUE 取排名

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只能取到其中一个