.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Using Ranking Function

Posted By:Ravi Ranjan Kumar       Posted Date: June 29, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

This article enable you to learn how to use Ranking function in SQL server for display managed data.


You can use ranking function to generate sequential numbers for each row or to give a rank based on specific criteria. For example, in a manufacturing organization, the management wants to rank the employee based on their salary. To rank the employees you must use of the rank function.

Ranking function returns a ranking value for each row. However, based on the criteria, more than one row can get the same rank. You can use the following function to rank the records.

  • row_number
  • rank
  • dense_rank

All these functions make use of the OVER clause. This clauses determines the ascending or descending sequences in which rows are assigned a rank.

Lets take a look one by one 

Row_number Functions

The row_number function returnds the sequential numbers,starting at 1 for the rows in a results set based on a column.

For example,the following SQL query display the sequential number on a column by using the row_number function

-- Using row_number function 
select EmployeeID,Rate,row_number() over(order by Rate desc) as Rank 
from HumanResources.EmployeePayHistory

The Followinf figure display the output of the preceding query 

The EmployeeID and Rate column are retrieved from the EmployeePayHistory table where the rate column is ranked by using the row_number function/ the ORDER BY keyword in the OVER clause specifies that the result set will appear in the descending order of the Rate column.


The rank function returns the rank of each row in a result set based on specified criteria. For example, you want to rank the procedure based on the sales made during a year. For this, you can use the rank function. This function will consider the ORDER BY clause and this record with maximum value will get the  higest rank if the ORDER BY Clause is asc.

Consider the Following example, you need to create the rport of all employee with their salary rates. The salary rates should also contain the rank of the salary. The highest ranked employee should be given the rank as 1. In addition, if two employees have te same salary rate, they should be given the same rank.

However next rank number will be addition of the rank of the employees that have same salary and the number of employees holding the same salary.

To perform this rank you need to write the following query 

-- Using rank() function 
select EmployeeID,Rate,rank() over(order by Rate desc) as Rank 
from HumanResources.EmployeePayHistory
The Following figure displays the output of the preceding query

The Followinf figure display the output of the preceding query 

Dense_rank function

The dense_rank() function is used where consecutive ranking values need to be given based on a specified criteria. It perform same ranking task as the rank function, but provides consecutive ranking values to an output.

For Example, you want to rank the products based on the sales done for the product during a year. If two products A and B have same sale values, both will be assigned a common rank. The next product in the order of sales values would be assigned the next rank value.

If in the preceding example of the rank function, you need to gives the same rank to the employees with the same salary rate and the consecutive rank to the next one.

To Perform this task you need to write the following query 

-- Using dense_rank function 
select EmployeeID,Rate,dense_rank() over(order by Rate desc) as Rank 
from HumanResources.EmployeePayHistory

the folloing figure displays the output of the preceeding query 

About Database 
 All query is related to AdventureWorks database. it come as free distributional package with SQL server. if you have not attached it in your SQL server. you can download it after an easy search on Google. 

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend