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 }