ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()
Sample
| 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; |
Result
| 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 |
출처 : SQL Server 2005 온라인 설명서(2007년 9월)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/tsqlref9/html/e7f917ba-bf4a-4fe0-b342-a91bcf88a71b.htm
(0) 
(0)Posted by 좐군
