SQL Ranking Functions – RANK(), DENSE_RANK(), ROW_NUMBER()

🔹 Why Ranking Functions?

Ranking window functions assign ordered numbers to rows based on a specified sort expression. They are essential for leaderboards, pagination, top-N analysis, and identifying duplicates.

⚙️ Basic Pattern

SELECT
  column_list,
  RANK() OVER (
    PARTITION BY partition_column
    ORDER BY sort_column DESC
  ) AS rank_value
FROM table_name;

• PARTITION BY: optional grouping (per department, per customer, etc.).
• ORDER BY: defines the ranking order.
• Each function handles ties differently.

🧠 ROW_NUMBER()

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

🧠 RANK()

SELECT
  employee_name,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS rank_pos
FROM employees;

🧠 DENSE_RANK()

SELECT
  employee_name,
  department,
  salary,
  DENSE_RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dense_rank
FROM employees;

🧩 Top 3 Salaries per Department

WITH RankedSalaries AS (
  SELECT
    department_id,
    employee_name,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS rank_in_dept
  FROM employees
)
SELECT *
FROM RankedSalaries
WHERE rank_in_dept <= 3;

🧠 Pagination with ROW_NUMBER

SELECT *
FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY order_date DESC) AS row_num,
    order_id,
    customer_id,
    total_amount
  FROM orders
) AS temp
WHERE row_num BETWEEN 11 AND 20;

⚖️ Comparison Table

💡 Interview Soundbite