SQL Server 2005 ranking functions – RANK(), DENSE_RANK(), NTILE()

RANK(), DENSE_RANK() and NTILE() are newly added functions in SQL Server 2005 T-SQL syntax. Another ranking function is ROW_NUMBER() that I have blogged earlier.

RANK() returns the rank of each row within the partition of a result set. When there is a tie, the same rank is assigned to the tied rows.

For example, 1, 2, 3, 3, 3, 6, 7, 7, 9, 10

DENSE_RANK() works like RANK(), except that the numbers being returned are packed (do not have gaps) and always have consecutive ranks.

For example, 1, 2, 3, 3, 3, 4, 5, 5, 6, 7

NTILE(integer_expression) breaks the rows within a partition into groups, while the number of groups is specified by "integer_expression". This is useful when percentile rank is required.

NTILE(N) returns 1 for the rows in the first group, 2 for those in the second group, and returns N for the last (N-th) group. Each group contains the same number of rows, or, if the number of rows in a partition is not divisible by "integer_expression", lower-numbered groups (starting from 1, 2, …) will each contain one more row. For example,

NTILE(2): 1, 1, 1, 1, 1, 2, 2, 2, 2, 2
NTILE(3): 1, 1, 1, 1, 2, 2, 2, 3, 3, 3
NTILE(4): 1, 1, 1, 2, 2, 2, 3, 3, 4, 4
NTILE(5): 1, 1, 2, 2, 3, 3, 4, 4, 5, 5
NTILE(6): 1, 1, 2, 2, 3, 3, 4, 4, 5, 6

The basic syntax follows.

ROW_NUMBER()  OVER ([<partition_by_clause>] <order_by_clause>)

RANK()  OVER ([<partition_by_clause>] <order_by_clause>)

DENSE_RANK()  OVER([<partition_by_clause>]<order_by_clause>)

NTILE(integer_expression)  OVER ([<partition_by_clause>] <order_by_clause>)

==========
Putting it all together, the following query shows all ranking functions in action using the famous AdventureWorks OLTP database!

SELECT i.ProductID
  , ProductName = p.Name
  , i.LocationID
  , i.Quantity
  , RowNumber = ROW_NUMBER()
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity)
  , Quartile = NTILE(4) 
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity)
  , Rank = RANK() 
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity)
  , DenseRank = DENSE_RANK() 
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity)
 FROM Production.ProductInventory i 
       INNER JOIN Production.Product p 
             ON i.ProductID = p.ProductID
 WHERE i.LocationID in (3, 4, 5)
 ORDER BY i.LocationID, RowNumber;

—– here comes the query result —–
(ProductName is intentionally removed for better presentation)

ProductID   LocID Quantity RowNumber  Quartile Rank DenseRank
———– —– ——– ———- —– ———- ——
492         3     17       1          1     1          1
496         3     30       2          1     2          2
493         3     41       3          2     3          3
494         3     49       4          3     4          4
495         3     49       5          4     4          4
494         4     12       1          1     1          1
492         4     14       2          1     2          2
493         4     24       3          2     3          3
496         4     25       4          3     4          4
495         4     35       5          4     5          5
317         5     158      1          1     1          1
318         5     171      2          1     2          2
351         5     179      3          1     3          3
319         5     184      4          1     4          4
952         5     192      5          1     5          5
400         5     260      6          1     6          6
815         5     265      7          1     7          7
401         5     283      8          1     8          8
352         5     300      9          1     9          9
488         5     318      10         1     10         10
477         5     323      11         1     11         11
476         5     324      12         1     12         12
949         5     336      13         1     13         13
487         5     337      14         2     14         14
950         5     342      15         2     15         15
332         5     344      16         2     16         16
945         5     347      17         2     17         17
948         5     347      18         2     17         17
951         5     348      19         2     19         18
802         5     350      20         2     20         19
803         5     356      21         2     21         20
804         5     363      22         2     22         21
399         5     366      23         2     23         22
398         5     372      24         2     24         23
320         5     372      25         2     24         23
484         5     374      26         2     26         24
481         5     374      27         3     26         24
479         5     390      28         3     28         25
816         5     406      29         3     29         26
327         5     408      30         3     30         27
819         5     409      31         3     31         28
482         5     427      32         3     32         29
485         5     427      33         3     32         29
818         5     428      34         3     34         30
821         5     432      35         3     35         31
817         5     443      36         3     36         32
820         5     446      37         3     37         33
486         5     515      38         3     38         34
480         5     515      39         3     38         34
483         5     531      40         4     40         35
316         5     532      41         4     41         36
321         5     540      42         4     42         37
330         5     548      43         4     43         38
329         5     558      44         4     44         39
328         5     568      45         4     45         40
323         5     568      46         4     45         40
324         5     568      47         4     45         40
478         5     568      48         4     45         40
331         5     574      49         4     49         41
322         5     587      50         4     50         42
350         5     622      51         4     51         43

==========
For more information

– NTILE { http://msdn2.microsoft.com/en-us/library/ms175126.aspx }

– RANK { http://msdn2.microsoft.com/en-us/library/ms176102.aspx }

– DENSE_RANK { http://msdn2.microsoft.com/en-us/library/ms173825.aspx }

– ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }

– Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }

– OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }

– ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }

– What's New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }

– AdventureWorks – SQL Server 2005 Samples and Sample Databases (July 2006) { http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en }

2 Replies to “SQL Server 2005 ranking functions – RANK(), DENSE_RANK(), NTILE()”

Leave a Reply

Your email address will not be published. Required fields are marked *