Sql中Rank排名函数

B.对结果集中的所有行排名

下面的示例返回按薪金排名的前十名员工。 因为未指定 PARTITION BY 子句，所以，RANK 函数应用于结果集中的所有行。

USE AdventureWorks2012
SELECT TOP(10) BusinessEntityID, Rate, 
RANK() OVER (ORDER BY Rate DESC) AS RankBySalary
FROM HumanResources.EmployeePayHistory AS eph1
WHERE RateChangeDate = (SELECT MAX(RateChangeDate) 
                        FROM HumanResources.EmployeePayHistory AS eph2
                        WHERE eph1.BusinessEntityID = eph2.BusinessEntityID)
ORDER BY BusinessEntityID;

下面是结果集：

BusinessEntityID Rate                  RankBySalary
---------------- --------------------- --------------------
1                125.50                1
2                63.4615               4
3                43.2692               8
4                29.8462               19
5                32.6923               16
6                32.6923               16
7                50.4808               6
8                40.8654               10
9                40.8654               10
10               42.4808               9