SQL Server Ranking Functions
SQL Server introduced four new ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set.
1.ROW_NUMBER ()
2.RANK ()
3.DENSE_RANK ()
4.NTILE ()
SQL Server Ranking Functions:-
SQL Server introduced four new ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set.Ranking functions are a subset of the built in functions in SQL Server.
ROW_NUMBER:-
Returns the serial number of the row order by specified column.starting at 1 for the first row in each partition.
RANK:-
Returns the rank of each row within the partition of a result set.
DENSE_RANK:-
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
NTILE:-
Distributes the rows in an ordered partition into a specified number of groups.
Syntax:-
ROW_NUMBER()/RANK()/DENSE_RANK()/NTILE() OVER (PARTITION BY Column 1, Column 2,.. ORDER BY Column 1, Column 2... )
PARTITION BY:-
Divides the result set produced by the FROM clause into partitions
Sample Table:-
SELECT * FROM Employee
Query 1 :-
SELECT employee_id,
first_name,
last_name,
salary,
joining_date,
department,
ROW_NUMBER()
OVER (
ORDER BY salary ) AS [Row_Number],
RANK()
OVER (
ORDER BY salary ) AS [Rank],
DENSE_RANK()
OVER (
ORDER BY salary ) AS [Dense_Rank],
NTILE(2)
OVER (
ORDER BY salary ) AS [Ntile]
FROM employee
Query 2 :-
SELECT employee_id,
first_name,
last_name,
salary,
joining_date,
department,
ROW_NUMBER()
OVER (
partition BY department
ORDER BY salary ) AS [Row_Number],
RANK()
OVER (
partition BY department
ORDER BY salary ) AS [Rank],
DENSE_RANK()
OVER (
partition BY department
ORDER BY salary ) AS [Dense_Rank],
NTILE(2)
OVER (
partition BY department
ORDER BY salary ) AS [Ntile]
FROM employee