SQL RANK() Function

Source: DataDriven analysis of 1,042 verified data engineering interview rounds.

RANK() Syntax

RANK is a window function. It requires an OVER clause with ORDER BY. PARTITION BY is optional. Without PARTITION BY, the entire result set is treated as one group.

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;

How RANK Handles Ties

If you remember nothing else from this page, remember that ties are the entire point of RANK. You'll see ORDER BY salary DESC in almost every interview example, and the moment two rows tie, RANK assigns the same number and jumps ahead. That gap tells you how many rows shared the spot. Five students at score 90 all get rank 2, and the next student gets rank 7. The formula is simple: next rank equals current rank plus number of tied rows.

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

RANK with PARTITION BY

PARTITION BY creates independent ranking windows. Each partition starts its ranking at 1. This is how you answer "rank employees within each department" or "top N per group" questions. Without PARTITION BY, every row competes against every other row globally. With it, rows only compete against rows in the same partition.

Engineering and Marketing each have their own ranking sequence. Bob and Carol tie at rank 2 within Engineering, so Dave gets rank 4 within Engineering. Marketing starts fresh at rank 1 for Eve. The partitions are completely independent.

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

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;

When to Use RANK()

RANK is the right choice when ties should share a position and the gap after ties communicates meaningful information. If you need consecutive numbers, use DENSE_RANK. If you need exactly one row per group, use ROW_NUMBER.

SELECT
  salesperson_id,
  region,
  total_revenue,
  RANK() OVER (
    PARTITION BY region
    ORDER BY total_revenue DESC
  ) AS revenue_rank
FROM quarterly_sales
WHERE fiscal_quarter = '2026-Q1';

SELECT
  student_id,
  exam_score,
  RANK() OVER (ORDER BY exam_score DESC) AS score_rank,
  COUNT(*) OVER () AS total_students
FROM exam_results;
-- If score_rank = 15 and total_students = 200,
-- exactly 14 students scored higher

WITH ranked AS (
  SELECT
    product_id,
    category,
    units_sold,
    RANK() OVER (
      PARTITION BY category
      ORDER BY units_sold DESC
    ) AS sales_rank
  FROM product_sales
)
SELECT * FROM ranked WHERE sales_rank <= 3;
-- May return 4+ rows per category if ties exist at rank 3

RANK vs DENSE_RANK vs ROW_NUMBER

All three are ranking window functions. They only differ in how they handle ties. Picking the wrong one is a silent bug: the query runs, returns results, and the interviewer waits to see if you notice.

3 RANK Interview Questions

These questions test whether you understand RANK beyond the textbook definition. Each includes what the interviewer is actually evaluating and how to structure your answer.

RANK and NULL Values

NULLs in the ORDER BY column affect rank assignments. The position of NULLs depends on the engine. PostgreSQL places NULLs last for ASC and first for DESC by default. SQL Server places NULLs first for ASC. Multiple NULL rows are treated as ties and receive the same rank.

-- 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

SQL RANK Function FAQ

Related Guides