SQL ROW_NUMBER() OVER函数的基本用法用法

语法：

ROW_NUMBER() OVER(PARTITION BY GroupingColumnName ORDER BY WithinGroupOrderbyColumnName)

按GroupingColumnName分组后，在组内按照指定（或默认）的排序规则生成行号。

 

e.g

SELECT * FROM GreekGod

ID          FullName             Gender
----------- -------------------- ----------
1           Apollo               MALE
2           Hera                 FEMALE
3           Zeus                 MALE
4           Venus                FEMALE

 

SELECT *,ROW_NUMBER() OVER(PARTITION BY Gender ORDER BY FullName DESC) ROWNUM FROM GreekGod

ID          FullName             Gender     ROWNUM
----------- -------------------- ---------- --------------------
4           Venus                FEMALE     1
2           Hera                 FEMALE     2
3           Zeus                 MALE       1
1           Apollo               MALE       2

 

SELECT * FROM
(SELECT *,ROW_NUMBER() OVER(PARTITION BY Gender ORDER BY FullName DESC) ROWNUM FROM GreekGod) TResult
WHERE ROWNUM=1

ID          FullName             Gender     ROWNUM
----------- -------------------- ---------- --------------------
4           Venus                FEMALE     1
3           Zeus                 MALE       1