Advanced SQL: Ranking Rows

Ranking functions are a type of window function in SQL. They let you assign a numeric rank to each row in a result set based on a specific order. Unlike regular aggregate functions (like SUM() or COUNT()), they don't collapse your rows; they add a new column with the rank to each existing row.

---

The Core Concept: The OVER() Clause

Ranking functions work over a "window" of data that you define using the OVER() clause. This clause tells the function how to group and order the data before ranking.

It has two key parts:

• PARTITION BY (Optional): This divides your data into groups. The ranking will be applied independently to each group and will restart at 1 for every new group.

• ORDER BY (Required): This sorts the rows within each group. The rank is determined by this order.

Let's use this sample Employees table for all our examples:

---

Key Ranking Functions

1. ROW_NUMBER()

This function assigns a unique, sequential number to each row. It doesn't care about ties; it just keeps counting.

• Use Case: Creating a unique row identifier for tasks like pagination or deleting a specific duplicate.

Example Query:

SELECT
    Name,
    Department,
    Salary,
    ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS DeptRowNumber
FROM Employees;

Result:

The numbering starts over when the Department changes from "Engineering" to "Sales". Even though Charlie and David have the same salary, they get unique row numbers (1 and 2).

2. RANK()

This function assigns a rank, but if there's a tie, it gives the tied rows the same rank and then skips the next number(s).

• Use Case: Creating a classic leaderboard where you want to see the gaps caused by ties (e.g., two people tie for 1st, so the next person is 3rd).

Example Query:

SELECT
    Name,
    Department,
    Salary,
    RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

Result:

In Engineering, Charlie and David both get rank 1. The function then skips rank 2, so Frank gets rank 3. The same logic applies to the Sales department.

3. DENSE_RANK()

This is like RANK(), giving tied rows the same rank, but it does not skip numbers. The sequence is always consecutive.

• Use Case: When you want to rank items with ties but need a gapless sequence.

Example Query:

SELECT
    Name,
    Department,
    Salary,
    DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS DenseSalaryRank
FROM Employees;

Result:

In Engineering, after the tie for rank 1, Frank gets rank 2. There are no gaps in the ranking sequence.

4. NTILE(n)

This function divides rows into a specified number (n) of roughly equal groups or "buckets."

• What if it's not divisible? NTILE handles uneven numbers by making the first few groups larger. For example, NTILE(2) on 5 rows will create a group of 3 and a group of 2.

• Use Case: Segmenting data into quartiles (NTILE(4)), deciles (NTILE(10)), or other percentile groups.

Example Query: Divide employees in each department into two salary tiers.

SELECT
    Name,
    Department,
    Salary,
    NTILE(2) OVER(PARTITION BY Department ORDER BY Salary DESC) AS SalaryTier
FROM Employees;

Result:

The Engineering department has 3 people, so the first tier gets the extra person (2 people in tier 1, 1 in tier 2). The Sales department divides evenly (2 people in each tier).

---

How to Use the Ranked Results

You cannot use a ranking function directly in a WHERE clause. The correct way to filter by a rank is a two-step process using a Common Table Expression (CTE).

1. Step 1: Create the rank in a CTE.

2. Step 2: Select from the CTE and apply your filter.

Goal: Find the highest-paid employee(s) in each department.

Complete Query:

-- Step 1: Define the CTE to rank the employees
WITH RankedEmployees AS (
    SELECT
        Name,
        Department,
        Salary,
        RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank
    FROM Employees
)
-- Step 2: Select from the CTE where the rank is 1
SELECT
    Name,
    Department,
    Salary
FROM RankedEmployees
WHERE SalaryRank = 1;

What Happens:

1. The database first runs the query inside the WITH block, creating a temporary result set that looks exactly like our RANK() example table above, with the SalaryRank column.

2. Then, the final SELECT statement runs against that temporary result set, filtering it to only include rows WHERE SalaryRank = 1.

Final Output: This query gives you the top-salaried employees from each department, correctly handling the ties.