SQL RANK() Function: Ties and PARTITION BY

RANK() OVER (
  [PARTITION BY partition_column]
  ORDER BY sort_column [ASC | DESC]
)

-- Basic RANK: global ranking by score
SELECT
  student_name,
  exam_score,
  RANK() OVER (ORDER BY exam_score DESC) AS score_rank
FROM exam_results;

-- RANK with PARTITION BY: ranking per department
SELECT
  department,
  employee_name,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank
FROM employees;

SELECT
  student_name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rnk
FROM exam_results;

-- Alice  95  1
-- Bob    90  2
-- Carol  90  2
-- Dave   85  4   <-- rank 3 is skipped
-- Eve    80  5

Key insight: Bob and Carol both score 90 and both get rank 2. Dave gets rank 4, not 3. Rank 3 does not exist because positions 2 and 3 were both consumed by the tie. This is the gap that interviewers ask about.

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

-- Multiple PARTITION BY columns
SELECT
  department,
  fiscal_year,
  employee_name,
  sales_total,
  RANK() OVER (
    PARTITION BY department, fiscal_year
    ORDER BY sales_total DESC
  ) AS yearly_sales_rank
FROM annual_sales;

-- Control NULL placement explicitly
SELECT
  employee_name,
  salary,
  RANK() OVER (
    ORDER BY salary DESC NULLS LAST
  ) AS salary_rank
FROM employees;

-- NULLs are ranked together at the bottom
-- All NULL salary rows share the same rank

Best practice: Always use NULLS LAST or NULLS FIRST explicitly when your ORDER BY column might contain NULLs. In engines that do not support this syntax (like older MySQL versions), use COALESCE to replace NULLs with a sentinel value.