Window Functions for Ranking Data

The Foundation of Ranking: ORDER BY within the OVER() Clause

While aggregate window functions like SUM() or AVG() can work on an unordered partition, ranking functions are meaningless without a defined order. You can't determine who is "1st", "2nd", or "3rd" without first sorting the data. Therefore, for all ranking functions, the ORDER BY sub-clause within OVER() is mandatory.

Let's watch a brief introduction that explains this core requirement.

The basic syntax will look like this:

SELECT
  column_a,
  column_b,
  RANKING_FUNCTION() OVER (PARTITION BY ... ORDER BY ...) AS rank_column
FROM your_table;

With this foundation, let's explore the three main ranking functions. They all serve a similar purpose but have crucial differences in how they handle ties (rows with the same value in the ORDER BY column).

The Three Core Ranking Functions

The best way to understand these functions is to see them in action and compare their behavior directly.

Let's summarize the behavior you just saw.

1. ROW_NUMBER()

This function assigns a unique, sequential integer to every row within its partition, starting from 1.

• Tie Handling: It does not handle ties in any special way. If two rows have the same value in the ORDER BY column, ROW_NUMBER() will still assign them different, sequential numbers. From a programming perspective, think of it as simply assigning a line number after sorting.
• Important Note: The order assigned to tied rows is not guaranteed to be consistent across different query executions unless you add a second, unique column to the ORDER BY clause to act as a tie-breaker (e.g., ORDER BY salary DESC, employee_id ASC).

2. RANK()

This function assigns a rank to each row based on the ORDER BY value.

• Tie Handling: Rows with the same value receive the same rank. However, the function then leaves a "gap" in the sequence. If two employees tie for 2nd place, they both get rank 2, and the next employee gets rank 4 (rank 3 is skipped).
• Analogy: This is like medal rankings in the Olympics. If two athletes tie for a gold medal (rank 1), no silver medal (rank 2) is awarded; the next athlete receives a bronze (rank 3).

3. DENSE_RANK()

This function also assigns a rank based on the ORDER BY value, handling ties identically to RANK().

• Tie Handling: Rows with the same value receive the same rank. The crucial difference is that DENSE_RANK() does not leave a gap in the sequence. If two employees tie for 2nd place, they both get rank 2, and the next employee gets rank 3.
• Use Case: This is ideal for creating "Top 10" leaderboards where you want exactly 10 unique ranks, even if there are ties.

Side-by-Side Comparison

To solidify your understanding, let's review a static comparison table.

Here is a visual summary of the behavior you've just reviewed:

SELECT
  player_name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS RowNum,
  RANK()       OVER (ORDER BY score DESC) AS Rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS DenseRank
FROM scores;

High-ROI Use Cases: From Theory to Practice

Understanding the mechanics is one thing; applying them to solve real problems is what makes these functions valuable for your career. Given your interest in practical, high-ROI skills, let's look at two extremely common scenarios where ranking functions are the go-to solution.

The technique shown in the video, using a Common Table Expression (CTE) or a subquery to first assign a rank and then filter on that rank, is the standard pattern for solving these problems.

For example, to find the top 3 highest-paid employees in each department:

WITH RankedEmployees AS (
  SELECT
    employee_name,
    department,
    salary,
    -- DENSE_RANK is often best for "Top N" to avoid skipping ranks
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
  FROM employees
)
SELECT
  employee_name,
  department,
  salary,
  salary_rank
FROM RankedEmployees
WHERE salary_rank <= 3;

This two-step process (rank, then filter) is necessary because you cannot use a window function directly in a WHERE clause.

Conclusion

Today we've added a powerful set of tools to your SQL arsenal. Ranking functions are essential for any kind of analysis that involves ordering and positioning, from simple leaderboards to complex data quality checks.

Key Takeaways:

• Ranking functions (ROW_NUMBER, RANK, DENSE_RANK) require an ORDER BY clause within the OVER() clause to define the sorting criteria.
• ROW_NUMBER() assigns a unique sequential number to every row, ignoring ties.
• RANK() assigns the same rank to tied rows and leaves a gap in the sequence.
• DENSE_RANK() assigns the same rank to tied rows but does not leave a gap, making it ideal for "Top-N" lists.
• A common and powerful pattern is to use a CTE or subquery to assign ranks and then filter on the rank number in the outer query.

Preview of the Next Lesson:
We've now seen how to calculate aggregates over a window (SUM, AVG) and how to determine a row's position within a window (RANK). What if we want to access data from a different row within the same window? In our next lesson, we will explore LAG() and LEAD(), two powerful window functions that allow you to access data from preceding or succeeding rows, enabling you to calculate things like year-over-year growth or the time difference between consecutive events.