With the use of SQL's ranking functions, you may give each row in a result set's partition a unique ranking. These functions come in very handy when you have to determine the row order according to certain standards. The RANK(), DENSE_RANK(), and ROW_NUMBER() functions are the three main ranking functions. Despite their apparent similarity, they exhibit different habits, particularly with regard to handling ties. We'll examine these functions in-depth and provide a useful example to illustrate how they differ in this post.

RANK()
The RANK() function assigns a unique rank to each row within a partition of a result set, with gaps in the ranking sequence where there are ties. This means that if two or more rows have the same value in the ordering column(s), they will be assigned the same rank, but the next rank will be incremented by the number of tied rows.

Syntax
RANK() OVER (
    PARTITION BY column1, column2, ...
    ORDER BY column1, column2, ...
)

Example
SELECT
    Name,
    Score,
    RANK() OVER (ORDER BY Score DESC) AS Rank
FROM
    Students;

Result

| Name    | Score | Rank |
|---------|-------|------|
| Alice   | 95    | 1    |
| Bob     | 85    | 2    |
| Charlie | 85    | 2    |
| Dave    | 75    | 4    |
| Eve     | 70    | 5    |

In this example, Bob and Charlie have the same score and are both ranked 2nd. The next rank, assigned to Dave, is 4th, leaving a gap at rank 3.

In this example, Bob and Charlie have the same score and are both ranked 2nd. The next rank, assigned to Dave, is 4th, leaving a gap at rank 3.
DENSE_RANK()

The DENSE_RANK() function is similar to RANK() but without gaps in the ranking sequence. When rows have the same value in the ordering column(s), they receive the same rank, but the next rank is incremented by one, regardless of the number of ties.

Syntax
DENSE_RANK() OVER (
    PARTITION BY column1, column2, ...
    ORDER BY column1, column2, ...
)

Example
SELECT
    Name,
    Score,
    DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM
    Students;

Result
| Name    | Score | DenseRank |
|---------|-------|-----------|
| Alice   | 95    | 1         |
| Bob     | 85    | 2         |
| Charlie | 85    | 2         |
| Dave    | 75    | 3         |
| Eve     | 70    | 4         |

Here, Bob and Charlie are both ranked 2nd, but the next rank is 3rd, assigned to Dave, with no gaps in the ranking sequence.

ROW_NUMBER()
The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition, without considering ties. Each row gets a distinct number, even if there are ties in the ordering column(s).
Syntax
ROW_NUMBER() OVER (
    PARTITION BY column1, column2, ...
    ORDER BY column1, column2, ...
)

Example
SELECT
    Name,
    Score,
    ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum
FROM
    Students;

Result

| Name    | Score | RowNum |
|---------|-------|--------|
| Alice   | 95    | 1      |
| Bob     | 85    | 2      |
| Charlie | 85    | 3      |
| Dave    | 75    | 4      |
| Eve     | 70    | 5      |

In this example, even though Bob and Charlie have the same score, they are assigned unique row numbers 2 and 3, respectively.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.