1. Introduction to Ranking Functions
Ranking Functions are useful to provide ranking on the table columns based on the value stored in it. Say, for example, let us say a table is having students id and their Total Marks as separate columns. Now, it is meaningful if we apply the these function on the Total Marks column rather than Student ID column. With these Functions, we can easily find 2nd maximum, or 4th maximum marks. The Ranking Functions are available in SQL 2005 and later versions.
Some useful Ranking Functions are displayed below:
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
- NTILE()
In this article, we will look how we can use those Ranking Functions by making use of NorthWnd Sample database.
2. SQL Demo View
First, we will create a view from the NorthWnd Db. We will use this view to study the Ranking Functions. Below is the view:
|
1 2 3 4 5 6 7 |
Create View NetSales as Select Ord.OrderId, Cat.CategoryName, Prod.ProductName, Ord.UnitPrice * Ord.Quantity as NetSales from (Categories cat INNER Join Products prod ON cat.CategoryId = Prod.CategoryId) INNER Join [Order Details] Ord ON ord.ProductId = Prod.ProductId; |
In the above view, we joined category, products and Order Details table together to get the required data. Also note that the NetSales computed column is nothing but the total sales of a product for an order id. We will perform Ranking Functions on this NetSales computed column. The result of the view is below. Note, the view does not have the order by clause as we showing it in the select query.
SQL Query
|
1 2 3 4 5 6 7 |
Select Ord.OrderId, Cat.CategoryName, Prod.ProductName, Ord.UnitPrice * Ord.Quantity as NetSales From (Categories cat INNER JOIN Products Prod ON cat.CategoryID = Prod.ProductID) INNER JOIN [Order Details] Ord ON Ord.ProductID = Prod.ProductID Order by Ord.OrderID; |
3. ROW_NUMBER Function
Row_Number applies the unique sequence of number for each row. This is useful when we want to delete duplicate rows on a table. Below example shows how we apply the Row_Number on the ascending ordered NetSales:
Here, rows marked under A, B and C have same NetSales values. But SQL Server applies the Row_Number sequentially with no repeats even the NetSales values are same. Note, we named ROW_NUMBER as SlNo in the above query.
SQL Query
|
1 2 |
Select ROW_NUMBER() over (Order By NetSales) as SlNo, * From NetSales; |
4. Apply RANK on NetSales
In the below example, we used a RANK Function to provide the rank for each product based on the generated NetSales. Note that we applied the Ranking Function called Rank on NetSales column in a descending order based on the values it holds. So, SQL Server gives the First Rank to the highest net sales and Second Rank to Next Highest and so on…
SQL Query
|
1 2 |
Select rank() over (Order by NetSales desc) as Rank, * From NetSales; |
In the above example, Set of Rows marked in A, B and C have same NetSales. As these groups have the same netsales value, they have the same rank. Also note the Gaps in the Rank say we do not have Rank 2, as there are two products in Rank 1.
The example below shows having different sorting order for Rank and Select Query’s statement. Note that the SQL Server still applies the ranking in the descending order of Netsales and it sorts the output based on the Product Name.
SQL Query
|
1 2 3 |
Select rank() over (Order by NetSales desc) as Rank, * From NetSales Order By ProductName; |
5. DENSE_RANK Ranking Function
The DENSE_RANK works same as rank function. The difference is that it avoids the gaps in the rank. Below example shows the DENSE_RANK rank in effect:
SQL Query
|
1 2 |
Select DENSE_RANK() over (Order By NetSales Desc) as Rank, * From NetSales; |
Note that even though the Rank 1 is shared between two rows, the next rank given to the NetSales of 10540.00 is 2 not 3. And this is how dense rank differs from the normal rank function.
6. NTILE Ranking Function (Percentile)
The next Ranking Function we will explore here is NTILE. NTILE Function performs Normal Distribution of the data. Say for example NTILE (100) means percentile of 100 and when we apply that on the Netsales in Descending orders, SQL Server distributes the sales in 100 groups. The top sales in NetSales value are placed on percentile 1 and the least sales in value is placed in percentile 100. Say, for example, if we call NTILE(5) in place of NTILE(100), then we are placing the Netsales in 5 groups stating TOP 5, meaning that RANK 1 group, Rank 2 Group, and so on till Rank 5 Groups.
In the below example the Netsales is distributed on a 100 percentile:
SQL Query
|
1 2 |
Select NTILE(100) over (Order By NetSales Desc) as Distribution, * From NetSales; |
7. Rank Functions and Group By Clause
In all the above examples, we used Ranking Functions on the full table of data. But we can use the rank functions for a group and rank will be reset when the group changes. In our example, we can try applying rank function for Netsales within each category. Doing so will rank the NetSales of product within each category. Below is the example for it:
SQL Query
|
1 2 3 4 5 6 |
Select Dense_Rank() Over ( Partition By CategoryName Order By NetSales Desc ) as Rank, * From NetSales; |
For all the Examples, the tables from NorthWnd Db is used. You can download it from here: Download
Categories: SQL Server
Tags: Percentile, Row_Number, SQL DENSE_RANK(), SQL NTILE, SQL Rank()
Do you like this Example? Please comment about it for others!!
This site uses Akismet to reduce spam. Learn how your comment data is processed.