13. ROW_NUMBER(), RANK(), DENSE_RANK()

Here are simple definitions, purposes, example queries, and a simulation using MS SQL for ROW_NUMBER(), RANK(), and DENSE_RANK():

1. ROW_NUMBER()

Definition:

The ROW_NUMBER() function assigns a unique sequential integer to rows within a result set, starting from 1 for the first row. It doesn’t leave gaps even if there are duplicate values.

Purpose:

Used when you need to assign a unique identifier to each row, for example, when paging or generating an ordered list of records.

Query Example:

SELECT 
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum, 
    EmployeeID, 
    FirstName, 
    LastName, 
    Salary 
FROM Employees;

Simulation:

Assume we have the following Employees table:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	5000
2	Jane	Smith	6000
3	Bob	Johnson	6000
4	Alice	Brown	4500

The query will return:

RowNum	EmployeeID	FirstName	LastName	Salary
1	2	Jane	Smith	6000
2	3	Bob	Johnson	6000
3	1	John	Doe	5000
4	4	Alice	Brown	4500

2. RANK()

Definition:

The RANK() function assigns a rank to each row in the result set based on a specified order. If two rows have the same value (tie), they will receive the same rank, but the next rank will skip one.

Purpose:

Used to assign ranks, where ties in values result in gaps in the rank sequence.

Query Example:

SELECT 
    RANK() OVER (ORDER BY Salary DESC) AS Rank, 
    EmployeeID, 
    FirstName, 
    LastName, 
    Salary 
FROM Employees;

Simulation:

Using the same Employees table:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	5000
2	Jane	Smith	6000
3	Bob	Johnson	6000
4	Alice	Brown	4500

The result of the RANK() query:

Rank	EmployeeID	FirstName	LastName	Salary
1	2	Jane	Smith	6000
1	3	Bob	Johnson	6000
3	1	John	Doe	5000
4	4	Alice	Brown	4500

Notice the gap between rank 1 and rank 3 due to the tie between Jane and Bob.

3. DENSE_RANK()

Definition:

The DENSE_RANK() function also assigns a rank to each row, but unlike RANK(), it does not leave gaps when there are ties. If two rows are tied for rank 1, the next rank will be 2.

Purpose:

Used when you want to rank rows without leaving any gaps in the ranking sequence, even if there are ties.

Query Example:

SELECT 
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank, 
    EmployeeID, 
    FirstName, 
    LastName, 
    Salary 
FROM Employees;

Simulation:

Again, using the same Employees table:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	5000
2	Jane	Smith	6000
3	Bob	Johnson	6000
4	Alice	Brown	4500

The result of the DENSE_RANK() query:

DenseRank	EmployeeID	FirstName	LastName	Salary
1	2	Jane	Smith	6000
1	3	Bob	Johnson	6000
2	1	John	Doe	5000
3	4	Alice	Brown	4500

In this case, no rank is skipped, even with the tie between Jane and Bob, so they both have rank 1, and the next rank is 2 (for John), then 3 (for Alice).

Summary of Differences:

Function	Handling of Ties	Gap in Ranking	Example Ranks
ROW_NUMBER()	No ties (unique number for each row)	No gap	1, 2, 3, 4
RANK()	Tied rows get the same rank	Yes (skips next rank after ties)	1, 1, 3, 4
DENSE_RANK()	Tied rows get the same rank	No gap	1, 1, 2, 3

These functions are useful for creating ranked data and managing how to handle ties in SQL queries.