Web Analytics Made Easy - Statcounter

Ranking Functions in SQL Server

Ranking Function:- Sometimes we need to provide a Row number to the rows in a table or within a partition. For example, suppose we want to give rank to sales man according to their sales amount in a particular month/year. For this purpose, SQL server provides us Ranking functions. Ranking functions are used to provide a Rank to a row in a given partition.

There are 4 Ranking functions provided by SQL server

1) ROW_NUMBER()

2) RANK ()

3) DENSE_RANK()

4) NTILE()

In this article, I am going to explain these functions with the help of the table “tbl_companysales”. Queries for creation and insertion of data into this table is given below:-

Create Table tbl_companysales(id int identity(1,1), employeeid int,  employeename nvarchar(150), month_name nvarchar(100), year int,salesamount int)

Insert Into tbl_companysales (employeeid,employeename,month_name,year,salesamount)
Select 1, ‘Vivek’ ,’April’, 2012, 200000
union all
Select 2, ‘Ravi’, ‘April’, 2012, 150000
union all
Select 3, ‘Uma’, ‘April’, 2012, 150000
union all
Select 4, ‘Raman’,’April’, 2012, 140000
union all
Select 1, ‘Vivek’, ‘October’, 2012, 300000
union all
Select 2, ‘Ravi’, ‘October’, 2012, 200000
union all
Select 3, ‘Uma’, ‘October’, 2012, 400000
union all
Select 4, ‘Raman’,’October’, 2012,300000
union all
Select 5, ‘Monika’, ‘October’, 2012, 300000
union all
Select  1, ‘Vivek’ , ‘November’, 2012, 300000
union all
Select 2, ‘Ravi’, ‘November’, 2012,200000
union all
Select 3, ‘Uma’, ‘November’, 2012, 200000
union all
Select 4, ‘Raman’, ‘November’, 2012, 200000
union all
Select  5, ‘Monilka’, ‘November’, 2012, 400000

We can use the following query to know the rows contains by this table

select  *   from tbl_companysales

 

Sample data for Ranking function Article

ROW_NUMBER():- This function gives the sequential number of a row within a partition of a resultset, starting from 1.

Syntax :-

ROW_NUMBER ( )     OVER ( [ ] )

Example:-  If we want to gives rank to employee within a month, we can use the following query

Query:-

SELECT employeename,month_name ,ROW_NUMBER () OVER (partition by month_name ORDER BY month_name) AS “Row Number”  ,salesamount FROM tbl_companysales

Result:-

This query will gives the following result:-

 

If we see the result, we found that the function ROW_NUMBER() gives every row a number within a partition which is in case is month.
RANK():- This function return the rank of a row within a partition of a result set

Syntax:-

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

Example:-Suppose if we want to get the rank of employee according to their sales amount within a month, we can use the following query

Query:-

SELECT employeename,month_name ,RANK() OVER (partition by month_name ORDER BY salesamount) AS Rank ,salesamount FROM tbl_companysales

Result:-
This query will gives the following result:-

 

If we see that result we found that RANK() function gives each rows a rank based on the sales amount value within a partition (in this month). Also we see that if more than one rows have the same sales amount then it gives same rank to all the rows containing the same sales amount value within a partition and for the next row it gives the next rank with a gap. For example in the above result, Vivek get the rank 4th in month April since it comes on 4th number instead of 3 and two rows in April with employees Ravi and Uma get the same rank.

DENSE_RANK():- This function returns the rank of a row within a partition of a result set similarly to Rank function but it return the rank of a row without any gap.

Syntax:-

DENSE_RANK ( )    OVER ( [ ] < order_by_clause > )

Example:-Suppose if we want to get the dense rank of employee according to their sales amount within a month, we can use the following query

Query:-

SELECT employeename,month_name ,DENSE_RANK() OVER (partition by month_name ORDER BY salesamount) AS “Dense Rank” ,salesamount FROM tbl_companysales

Result:-

This query will gives the following result:-

 

If we see the result , we found that Dense_Rank() function gives Rank without any gap as it gives 3rd rank to employee Vivek in month April.

NTILE():- This function distributes the rows in an ordered partition into a specified number of groups. For each row, NTILE() will returns the number of the group to which the row belongs.

Syntax:-

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

Example:-
In the following example, we divides the rows into 4 groups. since the number of rows are not fully divisible by 4 so it creates first two groups of 4 rows and next 2 groups of 3 rows each

Query:-

SELECT employeename,month_name ,NTILE(4) OVER (ORDER BY month_name) AS Quartile ,salesamount FROM tbl_companysales

Result:-

This query will gives the following result:-

 

If we see the result, we found that the Ntile() function divides the rows into 4 groups. Employees in the first group are given the rank 1 since they belong to first group. similarly are employees are given the rank according to the number of the group to which they belong.

Summary :- In this article , I tried to explain the concepts of Ranking functions in SQL Server with the help of examples. We mostly used the functions Row_number() and Rank(). This function is also used in deleting the duplicate records in a table. I am waiting for your valuable comments (feedback) on this article. If you have any query regarding this, you can send me a mail at askvivekjohari@gmail.com.

Keep reading and keep visiting my blog 🙂

 

DMCA.com


Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

30 Comments

Leave a Reply

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading