Welcome :Guest

Congratulations!!!

Top 5 Contributors of the Month
Melody Anderson

 Home >> Articles >> DataBase >> Post New Resource

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.

Introduction

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.

Rank

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