In SQL Programming, ranking functions are used to operate with numeric values. Programmer can easily perform all type of ranking functions to generate sequential numbers for each row based on specific criteria.
You can use ranking functions 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 employees based on their salary. To rank the employees, you can use the rank function.
Ranking function return 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 functions to rank the records:
For example, the following SQL query displays the sequential number on a column by using the row_number function:
SELECT BusinessEntityID, Rate, row_number ( ) OVER (ORDER BY Rate desc) AS RANK
FROM HumanResources.EmployeePayHistory
The following figure displays the output of the preceding query.
For example, you want to rank the products based on the sales done for that 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, 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 give the same rank to the employees with the same salary rate and the consecutive rank to the next one. You need to write the following query:
SELECT BusinessEntityID, Rate, dense_rank( ) OVER (ORDER BY Rate desc) AS rank
FROM HumanResources.EmployeePayHistory
You can use ranking functions 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 employees based on their salary. To rank the employees, you can use the rank function.
Ranking function return 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 functions to rank the records:
- row_number
- rank
- dense_rank
For example, the following SQL query displays the sequential number on a column by using the row_number function:
SELECT BusinessEntityID, Rate, row_number ( ) OVER (ORDER BY Rate desc) AS RANK
FROM HumanResources.EmployeePayHistory
The following figure displays 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 performs the 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 that 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, 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 give the same rank to the employees with the same salary rate and the consecutive rank to the next one. You need to write the following query:
SELECT BusinessEntityID, Rate, dense_rank( ) OVER (ORDER BY Rate desc) AS rank
FROM HumanResources.EmployeePayHistory
