SQL provides 4 ranking functions:
RANK — Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
DENSE_RANK –This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.
ROW_NUMBER — Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
NTILE — Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Examples
The following example shows the four ranking functions used in the same query. For function-specific examples, see each ranking function.
SELECT p.FirstName, p.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 AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
| FirstName | Row Number | Rank | Dense Rank | Quartile | SalesYTD | PostalCode |
|---|---|---|---|---|---|---|
| Michael | 1 | 1 | 1 | 1 | 4557045.0459 | 98027 |
| Linda | 2 | 1 | 1 | 1 | 5200475.2313 | 98027 |
| Jillian | 3 | 1 | 1 | 1 | 3857163.6332 | 98027 |
| Garrett | 4 | 1 | 1 | 1 | 1764938.9859 | 98027 |
| Tsvi | 5 | 1 | 1 | 2 | 2811012.7151 | 98027 |
| Shu | 6 | 6 | 2 | 2 | 3018725.4858 | 98055 |
| José | 7 | 6 | 2 | 2 | 3189356.2465 | 98055 |
| David | 8 | 6 | 2 | 3 | 3587378.4257 | 98055 |
| Tete | 9 | 6 | 2 | 3 | 1931620.1835 | 98055 |
| Lynn | 10 | 6 | 2 | 3 | 1758385.926 | 98055 |
| Rachel | 11 | 6 | 2 | 4 | 2241204.0424 | 98055 |
| Jae | 12 | 6 | 2 | 4 | 5015682.3752 | 98055 |
| Ranjit | 13 | 6 | 2 | 4 | 3827950.238 | 98055 |