Rank in SQL

 

In SQL, you can use the ROW_NUMBER() function to assign a unique row number to each row in the result set. Additionally, you can use RANK() to assign a ranking to rows based on a specific column's value.

Here’s an example query that demonstrates how to use ROW_NUMBER(), RANK(), and DENSE_RANK() to rank rows based on a column, such as desk.

SELECT 

    desk,

    ROW_NUMBER() OVER (ORDER BY desk) AS RowNumber,

    RANK() OVER (ORDER BY desk) AS DeskRank,

    DENSE_RANK() OVER (ORDER BY desk) AS DeskDenseRank

FROM 

    Employees;

Explanation:

ROW_NUMBER(): Assigns a unique row number starting from 1, incrementing by 1 for each row.

RANK(): Assigns a rank, but skips rank numbers if there are ties.

DENSE_RANK(): Similar to RANK(), but does not skip rank numbers in case of ties.

The RANK() function in SQL is used to assign a rank to each row within a partition of a result set. It is particularly useful when you want to rank data based on some order, and it can handle ties (when two or more rows have the same value in the ordered set). The RANK() function assigns the same rank to rows with identical values and then skips subsequent ranks.

Syntax:

RANK() OVER (

    [PARTITION BY column_name(s)]

    ORDER BY column_name [ASC|DESC]

)

Explanation of Components:

RANK(): The function itself, which will compute the rank of each row.

OVER(): Defines how the ranking is to be applied (by what criteria the rows are partitioned and ordered).

PARTITION BY (Optional): This clause allows you to divide the result set into partitions, and the ranking starts over in each partition. If omitted, the function treats the entire result set as one partition.

ORDER BY: Specifies the column(s) used to determine the rank. The ordering can be in ascending (ASC) or descending (DESC) order.

How Ranking Works:

Rows that have the same value in the ORDER BY clause are assigned the same rank.

The next rank after a tie is not sequential; it skips ahead by the number of rows with the tied rank.

Example:

Let’s say we have a table Employee with the following data:

Query using RANK():

SELECT Name, Department, Salary, 

       RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank

FROM Employee;

Result:

Explanation:

In the Sales department, Alice and Bob have the same salary, so they are both assigned a rank of 1. The next rank after them skips to 3 because the second rank is skipped.

In the HR department, Charlie has the highest salary and is ranked 1, while Dave is ranked 2.

Difference Between RANK(), DENSE_RANK(), and ROW_NUMBER():

RANK(): If there is a tie, the ranks are the same, but it skips the next rank(s).

DENSE_RANK(): Same as RANK(), but does not skip ranks after ties.

ROW_NUMBER(): No ties are considered, and rows are sequentially numbered.

Example:

-- Using DENSE_RANK()

SELECT Name, Department, Salary, 

       DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank

FROM Employee;

-- Using ROW_NUMBER()

SELECT Name, Department, Salary, 

       ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum

FROM Employee;

This way, you can rank rows in a flexible way dependin

g on how you want to handle ties and partitions in the result set.