Ranking Functions Examples – RANK, DENSE_RANK & Percentile

SQL Dense Rank Example
SQL Dense Rank Example

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:

  1. ROW_NUMBER()
  2. RANK()
  3. DENSE_RANK()
  4. 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:

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.

View used for this Examples
View used for this Examples

SQL Query

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:

Row_Number SQL Example
Row_Number SQL Example

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

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…

"Applying

SQL Query

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 Rank Order and Record Order
SQL Rank Order and Record Order

SQL Query

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 Dense Rank Example
SQL Dense Rank Example

SQL Query

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:

The SQL NTILE Function Example
The SQL NTILE Function Example

SQL Query

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:

"Applying

SQL Query

For all the Examples, the tables from NorthWnd Db is used. You can download it from here:  Download

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: