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

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Ranking Function, SQL Server and tagged , . Bookmark the permalink.

30 Responses to Ranking Functions in SQL Server

  1. Unknown says:

    Thanks.It is really nice.keep writing…

  2. Pingback: fragrances

  3. Pingback: dentist in Phoenix Arizona

  4. Pingback: coffee shops in Yakima, WA.

  5. Pingback: Istanaimpian2

  6. Pingback: Charlotte Johansson

  7. Pingback: https://www.wolfpackcryptos.io/

  8. Pingback: บาคาร่า

  9. Pingback: 더킹카지노

  10. Pingback: 서울출장안마

  11. Pingback: see this here

  12. Pingback: 바카라사이트

  13. Pingback: enterthenews.pl/forum/

  14. Pingback: รักษาหลุมสิว

  15. Pingback: 온라인카지노

  16. Pingback: Education - Excluding Post Secondary

  17. Pingback: เว็บคาสิโนออนไลน์

  18. Pingback: xembongdatructuyen

  19. Pingback: a knockout post

  20. Pingback: scam

  21. Pingback: w88

  22. Pingback: galeri qq

  23. Pingback: con heo dat remix

  24. Pingback: indoqqpoker

  25. Pingback: Where Does Gold Come From Youtube and Why is Gold Rare

  26. Pingback: Darknet

  27. Pingback: Nightmare Market

  28. Pingback: replica rolex rose gold watch

  29. Pingback: Agartha Market

  30. Pingback: Cider And Perry Market Tracking Report Analysis 2016 – 2026

Leave a Reply