Find Top N records per group

High-frequency SQL interview problem closely related to ROW_NUMBER(), RANK(), and DENSE_RANK() is:

👉 Find Top N records per group

This problem tests how well you understand window functions + PARTITION BY, not just syntax.

Let’s walk through it step by step.

🧠 Problem Statement

👉 Find the top 2 highest-paid employees in each department.

Sample data:

Employees table columns:

• emp_name
• department
• salary

Assume some employees in a department can have the same salary.

🔢 Attempt 1: Using ROW_NUMBER()

SELECT emp_name, department, salary
FROM (
    SELECT emp_name,
           department,
           salary,
           ROW_NUMBER() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rn
    FROM employees
) t
WHERE rn <= 2;

📌 Behavior of ROW_NUMBER()

• Assigns unique numbers within each department
• Breaks ties arbitrarily

✔ Use this when:

• You strictly want only N rows per group
• Ties do not matter

❌ Problem:
Employees with the same salary may get excluded.

🏁 Attempt 2: Using RANK()

SELECT emp_name, department, salary
FROM (
    SELECT emp_name,
           department,
           salary,
           RANK() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rnk
    FROM employees
) t
WHERE rnk <= 2;

📌 Behavior of RANK()

• Same salary → same rank
• Skips rank numbers when duplicates exist

✔ Use this when:

• You want competition-style ranking
• Rank gaps are acceptable

❌ Problem:
You may get more than N rows and missing ranks.

🎯 Best Approach: Using DENSE_RANK()

SELECT emp_name, department, salary
FROM (
    SELECT emp_name,
           department,
           salary,
           DENSE_RANK() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS drnk
    FROM employees
) t
WHERE drnk <= 2;

✔ Why DENSE_RANK() is ideal here

• Handles duplicate salaries correctly
• No rank gaps
• Returns top N salary levels, not just rows

This makes DENSE_RANK() the most interview-safe solution for Top-N-per-group problems.

📊 Comparison Summary

💡 Interview Rule of Thumb

• Pagination / strict limits → ROW_NUMBER()
• Leaderboard ranking → RANK()
• Top N per group / Nth highest problems → DENSE_RANK()

💬 Interview Question for You

How would you fetch the highest salary per department, including ties, without using subqueries?

Think window functions 👀