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


Ranking Functions

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


Ranking Functions


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


Ranking Functions


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: