How to Rank Results in SQL Server
Wouldn't it be nice to get away from using temporary tables to rank query results in SQL Server? The rank functions can help you accomplish this very easily. There are four rank functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE.
The ROW_NUMBER Function
The ROW_NUMBER function works similar to an identity column in a table. It will assign a unique number, starting at 1 and incrementing depending on the sort order specified. Duplicate rows will receive a unique number with this function.
The example below assigns a column value starting at 1 and incrementing according to the lastname column in ascending order.
-
SELECT ROW_NUMBER() over (ORDER BY lastname ASC), firstname, lastname
-
FROM person
-
ORDER BY lastname ASC, firstname ASC
The RANK Function
The main difference between the RANK function and the ROW_NUMBER function is how duplicates are treated. They are not assigned a unique number with RANK. The duplicates will each have the same number. The next unique row after two or more duplicates will have the next number in the series after skipping the appropriate number based on the number of duplicates.
The following example is the same as above, but with the RANK function.
-
SELECT RANK() over (ORDER BY lastname ASC), firstname, lastname
-
FROM person
-
ORDER BY lastname ASC, firstname ASC
The DENSE_RANK Function
The DENSE_RANK function is very similar to the RANK function, except after assigning a number to a series of duplicates, then next value will be n + 1. There are not gaps in the number when using this function.
The following example is similar to the two above, but with the DENSE_RANK function.
-
SELECT DENSE_RANK() over (ORDER BY lastname ASC), firstname, lastname
-
FROM person
-
ORDER BY lastname ASC, firstname ASC
The NTILE Function
The NTILE function is used to divide the resultset into a number of groups. Say you have 100 rows in your resultset and you use NTILE(4). You will end up with 4 groups of 25 rows, with each row of each group having a unique number assigned to that group.
The following example is the same query as the ones above, but splits the resultset into four groups using the NTILE function.
-
SELECT NTILE(4) over (ORDER BY lastname ASC), firstname, lastname
-
FROM person
-
ORDER BY lastname ASC, firstname ASC
The Partition By Clause
The partition by clause is used to further subdivide the resultset to be ranked by grouping on a particular column. Using the same query as above, let's add a zipcode. This will result in the following query when using the RANK function.
-
SELECT RANK() over (partition BY zipcode ORDER BY lastname ASC), firstname, lastname, zipcode
-
FROM person
-
ORDER BY zipcode ASC, lastname ASC, firstname ASC
This query will rank by the lastname in ascending order within each zipcode column. The partition by clause makes each of the functions above much more useful when applying a rank to query results.
Post comments