There are four types of Ranking Functions in SQL. All the Ranking Functions are used with Over Clause
- Row_Number: The row_number function gives each a row in a table a serial number.
Consider below Table named 'EmpDetails', the table contains two columns 'EmpID' and 'EmpName'
Now we will implement Row_number to the above table:
select *, ROW_NUMBER() over (order by empid) as Rowno from dbo.EmpDetails
The output of the query is as follows:
- Rank: The Rank function gives rank to each rows in the result based on the over clause.
select *, Rank() over (order by empid) as Rowno from dbo.EmpDetails
As seen above, the employees with empid as '1' has been given Rank 1, whereas the employee with empid '2' has rank 3, because there were two employees with empid 1 that has rank one.
- Dense_Rank: This function is similar to Rank() function, the only difference is that it gives Rank without any gaps.
select *, dense_Rank() over (order by empid) as Rowno from dbo.EmpDetails
If you observe the above result set, dense_rank() function has assigned rank 2 to the employees with Empid 2, unlike Rank() function which ranked them as 3.
- Ntile: The Ntile function requires to specify a number in the parentheses and based on that the it divides the rows in the result set.
select *, Ntile(2) over (order by empid) as Rowno from dbo.EmpDetails
Here, we have specified 2 in the parentheses, hence the result set with contain only two ranks i.e. 1 and 2