PARTITION BY and Ranking Functions

Ranking Within Groups

• “Find the top 3 products by revenue in each category.” 
• “Get the second-highest salary in each department.” 
• “Rank customers by total spend.”

We’ve lost count of how many times ranking questions appear in interviews. They’re popular because they test whether you actually understand window functions or just memorized some syntax. Let’s make sure you understand them.

Three Functions, Three Behaviors

SQL gives you three ranking functions. They all assign numbers based on ORDER BY, but they handle ties differently. Choosing the wrong one is a common mistake.

• ROW_NUMBER() assigns a unique number for every row. When ties happen, order is arbitrary (1, 2, 3, 4…).
• RANK() gives the same number for ties, then skips (1, 1, 3, 4, 4, 6…).
• DENSE_RANK() gives the same number for ties, no skip (1, 1, 2, 3, 3, 4…).

Seeing the Difference

Let me show you with actual data. We’ll rank employees by salary:

• salary 100000 → ROW_NUMBER = 1, RANK = 1, DENSE_RANK = 1
• salary 100000 → ROW_NUMBER = 2, RANK = 1, DENSE_RANK = 1
• salary 90000 → ROW_NUMBER = 3, RANK = 3, DENSE_RANK = 2
• salary 80000 → ROW_NUMBER = 4, RANK = 4, DENSE_RANK = 3
• salary 80000 → ROW_NUMBER = 5, RANK = 4, DENSE_RANK = 3
• salary 70000 → ROW_NUMBER = 6, RANK = 6, DENSE_RANK = 4

Two people earn 100k. ROW_NUMBER gives them 1 and 2 (arbitrary). RANK gives them both 1, then skips to 3. DENSE_RANK gives them both 1, then goes to 2.

Your First Ranking Function

SELECT
  cust_id,
  total_order_cost,
  ROW_NUMBER() OVER(
    PARTITION BY cust_id
    ORDER BY total_order_cost DESC
  ) AS rn
FROM orders;

If two people have the same salary, who gets 1 and who gets 2? It’s arbitrary unless you add more columns to ORDER BY. This can bite you if you’re not careful.

The “Top N Per Group” Pattern

This is the big one. Add PARTITION BY to restart numbering for each group:

SELECT 
    first_name,
    department,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS dept_rank
FROM employees;

Now HR gets its own 1, 2, 3… Admin gets its own 1, 2, 3… Perfect for finding top N in each group.

RANK vs DENSE_RANK: When Ties Matter

If you’re asked for “top 3 by revenue” and two items tie for 2nd place:

• RANK: positions 1, 2, 2, 4 → You get items at positions 1 and 2, but skip 3
• DENSE_RANK: positions 1, 2, 2, 3 → You get items at positions 1, 2, and 3

Which is “right” depends on the business question. Usually, when someone asks for “top 3,” they mean DENSE_RANK behavior: they want three tiers, even if ties mean more than three items.

WITH ranked AS (
  SELECT
    first_name,
    department,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS dept_rank
  FROM employee
)
SELECT *
FROM ranked
WHERE dept_rank <= 2;

You can’t filter on window functions directly in WHERE. You have to wrap it in a CTE or subquery first. This trips people up in interviews all the time.

Common Mistakes

Forgetting ORDER BY

-- Wrong: ranking without ORDER BY is meaningless
ROW_NUMBER() OVER (PARTITION BY department);

-- Right: always specify what you're ranking BY
ROW_NUMBER() OVER (
    PARTITION BY department 
    ORDER BY salary DESC
);

Filtering in WHERE

-- Wrong: window functions can’t be in WHERE
SELECT *
FROM employees
WHERE ROW_NUMBER() OVER (…) = 1;

-- Right: use CTE, then filter
WITH ranked AS (
    SELECT …,
           ROW_NUMBER() OVER (…) AS rn
    FROM employees
)
SELECT *
FROM ranked
WHERE rn = 1;

SELECT
  first_name,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_val
FROM employee;

Ranking Most Active Guests

Find top performers within groups. You’ll need ranking with aggregation here.

Combine ranking with a CTE to filter for top results.

Find the second-highest salary. Classic DENSE_RANK() with CTE pattern.

Key Takeaways

• ROW_NUMBER: unique numbers, arbitrary for ties
• RANK: ties get same number, then skip (1, 1, 3…)
• DENSE_RANK: ties get same number, no skip (1, 1, 2…)
• Always include ORDER BY in ranking functions
• Filter on ranks using a CTE or subquery, not WHERE

What’s Next

Ranking tells you position. But what about comparing to the previous row? What was last month’s revenue? How does this quarter compare to last? That’s LAG and LEAD, and they’re up next.