Ranking Function

You would like to identify and group the information available in the table based on condition, after grouping. You may wish to rank the people in each group based on their category. This result can be obtained by ranking grouped data

Type of Ranking function in sql server 2008

Using Rank function, you can add extra column in select statement with sequence number for each result set.

Example of RANKING function

RANK Function

Select  repid, qty, rank() over(partition by repid order by qty desc) ‘RANK’

From sales Where qty between 200 and 300

Rank values returned by rank function are not continuous

Select  repid, qty, Dense_ rank() over(partition by repid order by qty desc) ‘ Dense RANK’

From sales Where qty between 200 and 300

Dense Rank Function

Dense Rank function is similar to the rank function only difference is, it does not produce any gaps in between ranking values

Row_number Function

SELECT repid, qty, ROW_NUMBER() over (partition by repid order by qty desc) ‘Row number’

From sales Where qty between 200 and 300

Ntile

SELECT repid, qty, ntile(4) over (partition by repid order by qty desc) ‘Row number’

From sales

Where qty between 200 and 300

In this topic, we have used rank function to effectively rank information from rows and columns. Using rank functions, you can display the information from a database based on your preference.