Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted March 7, 2012

SQL Server Ranking Functions

By Deanna Dicken

There are four ranking functions included with SQL Server (starting with SQL Server 2005). Those functions; ROW_NUMBER, RANK, DENSE_RANK, and NTILE; can be used to rank the rows of your result set over a partition. This article will present those functions and examples of their use.

Ranking Functions

Ranking functions are a subset of the built in functions in SQL Server. They are used to provide a rank of one kind or another to a set of rows in a partition. The partition can be the full result set, if there is no partition. Otherwise, the partitioning of the result set is defined using the partition clause of the OVER clause. The order of the ranking within the partition is defined by the order clause of OVER. Order is required even though partition is optional. The OVER clause syntax can be found here.

In the following, each ranking function will be discussed and an example shown. The examples for this article use tables in the AdventureWorks2008R2 database.

ROW_NUMBER

The ROW_NUMBER ranking function is the simplest of the ranking functions. Its purpose in life is to provide consecutive numbering of the rows in the result set by the order selected in the OVER clause for each partition specified in the OVER clause.

If no partition is specified, ROW_NUMBER will provide a consecutive numbering of the rows based on the order clause. If a partition is provided, the numbering is consecutive within the partition and begins again at 1 when the partition changes.

Example

Here we have a query to find the top 10 products that sold in 2008. ROW_NUMBER is used here to designate the ordering from highest qty ordered to lowest. The order clause in the OVER clause tells it to rank the rows by the sum of all the order quantities for that product.

SELECT TOP(10) ProductID, SUM(OrderQty) AS ProductOrderCount, ROW_NUMBER() OVER (ORDER BY SUM(OrderQty) DESC) AS Row
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
WHERE OrderDate BETWEEN '1/1/2008' AND '12/31/2008'
GROUP BY ProductID
ORDER BY Row

Results

ProductID ProductOrderCount Row
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
870 3519 1
712 2376 2
711 2133 3
708 2064 4
707 2053 5
873 2051 6
864 1834 7
921 1782 8
884 1654 9
877 1475 10
(10 row(s) affected)

To give you an example of what ROW_NUMBER would do if there was a partition involved; consider this example where TerritoryID was added to the result set. In this case, we have to retrieve the top 20 so you can see the row number start over when the partition changes. The partition is by ProductID and the order by clause of the select statement changed to order the result set by ProductID and then Row. The GROUP BY clause also changed to include Territory ID. So, we are getting a summation of the OrderQty for each product in each territory. That result set then has the ROW_NUMBER applied to each product in the order of most quantity ordered in each territory. After the ranking is applied, the result set is ordered by ProductID and Row and the top 20 rows are returned.

SELECT TOP(20) ProductID, soh.TerritoryID,
SUM(OrderQty) AS ProductOrderCount,
ROW_NUMBER() OVER ( PARTITION BY sod.ProductID ORDER BY SUM(OrderQty) DESC) AS Row
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
JOIN Sales.SalesTerritory st
ON soh.TerritoryID = st.TerritoryID
WHERE OrderDate BETWEEN '1/1/2008' AND '12/31/2008'
GROUP BY ProductID, soh.TerritoryID
ORDER BY ProductID, Row

Results

ProductID TerritoryID ProductOrderCount Row
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
707 4 355 1
707 6 324 2
707 9 283 3
707 10 249 4
707 1 243 5
707 7 217 6
707 8 211 7
707 3 66 8
707 5 59 9
707 2 46 10
708 4 374 1
708 6 332 2
708 9 288 3
708 1 236 4
708 10 223 5
708 8 208 6
708 7 182 7
708 2 92 8
708 3 65 9
708 5 64 10
(20 row(s) affected)

RANK

The function RANK provides consecutive numbering except in the case of a tie. This function is used when you want all tied rows to have the same ranking, but the next row after the tied rows to have the rank it would have been assigned if there had been no tie. In other words, the numbers assigned by RANK are not necessarily consecutive.

Example

Let's take the first example from above (products ordered in 2008) and apply RANK instead of ROW_NUMBER. To get a result worth looking at, however, we have to limit the products to those having a ProductOrderCount between 400 and 500. You can see in the results that two of the rows had ProductOrderCount equal to 488. Therefore, with RANK, they both received the same ranking, in this case 2, and the next number is skipped. In this example the 2nd and 3rd items have a ranking of 2 and the fourth item has a ranking of 4.

SELECT ProductID, SUM(OrderQty) AS ProductOrderCount,
RANK() OVER (ORDER BY SUM(OrderQty) DESC) AS Ranking
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
WHERE OrderDate BETWEEN '1/1/2008' AND '12/31/2008'
GROUP BY ProductID
HAVING SUM(OrderQty) BETWEEN 400 AND 500
ORDER BY Ranking

Results

ProductID ProductOrderCount Ranking
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
928 499 1
954 488 2
966 488 2
974 471 4
972 464 5
933 463 6
801 453 7
977 447 8
797 442 9
860 409 10
(10 row(s) affected)

DENSE_RANK

As with RANK, DENSE_RANK will divvy out the same rank to tying rows. In contrast, however, DENSE_RANK will not skip any numbers. So if the first two rows tie, they will both receive 1 for the rank. The third row will receive 2 for its rank, unlike with RANK where it would have been assigned 3.

Example

To display the difference, here is the same example query as above. Changing RANK to DENSE_RANK renders the same results with one distinction. Rows 2 and 3 are still tied and therefore receive the same rank of 2; however the fourth row now receives the rank of 3 instead of 4.

SELECT ProductID, SUM(OrderQty) AS ProductOrderCount,
DENSE_RANK() OVER (ORDER BY SUM(OrderQty) DESC) AS Ranking
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
WHERE OrderDate BETWEEN '1/1/2008' AND '12/31/2008'
GROUP BY ProductID
HAVING SUM(OrderQty) BETWEEN 400 AND 500
ORDER BY Ranking

Results

ProductID ProductOrderCount Ranking
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
928 499 1
954 488 2
966 488 2
974 471 3
972 464 4
933 463 5
801 453 6
977 447 7
797 442 8
860 409 9
(10 row(s) affected)

NTILE

When you want to group rows equally across a partition, the NTILE function is the one you want. For this ranking function you need to provide it the number of groups into which you want the rows equally divided. Should the row count not be equally divisible by the number of groups, there will be a group or groups with one more row than the rest. Those groups with the extra row will come first in the ranking.

For instance, if you had 26 rows and desired 5 groups, the first group would contain 6 rows and the remaining 4 groups would contain 5 rows each.

Example

Again using the same query to identify products with a total quantity ordered in 2008 between 400 and 500, this time we are grouping them into 4 groups using NTILE and supplying the group count in the function call. Since this query returns 10 rows and 10 is not equally divided into 4 groups, you can see in the results that the first two groups contain 3 rows each and the last two groups contain just two rows each.

SELECT ProductID, SUM(OrderQty) AS ProductOrderCount,
NTILE(4) OVER (ORDER BY SUM(OrderQty) DESC) AS Ranking
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
WHERE OrderDate BETWEEN '1/1/2008' AND '12/31/2008'
GROUP BY ProductID
HAVING SUM(OrderQty) BETWEEN 400 AND 500
ORDER BY Ranking

Results

ProductID ProductOrderCount Ranking
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
928 499 1
954 488 1
966 488 1
974 471 2
972 464 2
933 463 2
801 453 3
977 447 3
797 442 4
860 409 4
(10 row(s) affected)

Conclusion

SQL Server includes a subset of built-in functions called ranking functions. These ranking functions provide various means of ranking and grouping result sets by utilizing the OVER clause to identify the partitioning of the result set and the order for the ranking.

Links to Additional Information

OVER

ROW_NUMBER

RANK

DENSE_RANK

NTILE

See all articles by Deanna Dicken



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date