Row_Number: The row_number function gives each a row in a table a serial number.

Example:





Consider below Table named 'EmpDetails', the table contains two columns 'EmpID' and 'EmpName'





Now we will implement Row_number to the above table:





Query:





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.





Example:

select *, Rank() over (order by empid) as Rowno from dbo.EmpDetails





Ouput:





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.

Example:





select *, dense_Rank() over (order by empid) as Rowno from dbo.EmpDetails





Output:





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.

Example:





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





Output:





















There are four types of Ranking Functions in SQL. All the Ranking Functions are used with Over Clause