Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

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

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...