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.