排名函數 (Transact-SQL)
排名函數會傳回資料分割中每個資料列各一個次序值。根據所用的函數而定，有些資料列可能會收到與其他資料列相同的值。排名函數不具決定性。
Transact-SQL 會提供下列排名函數：
範例
下列範例顯示在相同查詢中使用的四個排名函數。請從特定函數範例瞭解每種排名函數。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number'
,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'
,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank'
,NTILE(4) OVER (ORDER BY a.PostalCode) AS 'Quartile'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
以下為結果集：
FirstName |
LastName |
Row Number |
Rank |
Dense Rank |
Quartile |
SalesYTD |
PostalCode |
|---|---|---|---|---|---|---|---|
Maciej |
Dusza |
1 |
1 |
1 |
1 |
4557045 |
98027 |
Shelley |
Dyck |
2 |
1 |
1 |
1 |
5200475 |
98027 |
Linda |
Ecoffey |
3 |
1 |
1 |
1 |
3857164 |
98027 |
Carla |
Eldridge |
4 |
1 |
1 |
1 |
1764939 |
98027 |
Carol |
Elliott |
5 |
1 |
1 |
2 |
2811013 |
98027 |
Jauna |
Elson |
6 |
6 |
2 |
2 |
3018725 |
98055 |
Michael |
Emanuel |
7 |
6 |
2 |
2 |
3189356 |
98055 |
Terry |
Eminhizer |
8 |
6 |
2 |
3 |
3587378 |
98055 |
Gail |
Erickson |
9 |
6 |
2 |
3 |
5015682 |
98055 |
Mark |
Erickson |
10 |
6 |
2 |
3 |
3827950 |
98055 |
Martha |
Espinoza |
11 |
6 |
2 |
4 |
1931620 |
98055 |
Janeth |
Esteves |
12 |
6 |
2 |
4 |
2241204 |
98055 |
Twanna |
Evans |
13 |
6 |
2 |
4 |
1758386 |
98055 |