RANK()

Syntax

How it works

RANK() is a ranking window function. For every row it returns a number that says where the row sits in an ordered list, defined by the ORDER BY inside the OVER() clause. Unlike an aggregate, it does not collapse rows: every input row keeps its place and simply gains a rank column.

The defining behaviour of RANK() is how it treats ties. Rows that are equal under the ORDER BY receive the same rank. The next distinct value then skips ahead by the number of tied rows, so the sequence leaves gaps. Three people tied for first are all ranked 1, and the next person is ranked 4, not 2. A simpler tie of two rows produces 1, 1, 3.

Add PARTITION BY to rank inside groups: the counter resets to 1 at the start of every partition, which is how you answer questions like "rank each employee within their own department". This is the sibling of DENSE_RANK(), which ranks the same way but never leaves gaps, and ROW_NUMBER(), which ignores ties and always gives a unique number. Choosing between the three is one of the most common SQL interview topics.

Examples

Rank rows over the whole result set

-- Highest score gets rank 1
SELECT name, score,
       RANK() OVER (ORDER BY score DESC) AS rnk
FROM players;

name  | score | rnk
------+-------+----
 Ali  |    95 |   1
 Sara |    90 |   2
 Omar |    88 |   3
 Nour |    72 |   4

Ties share a rank and the next rank skips (the gap)

-- Two players tie on 90, so both are rank 2,
-- and the next player jumps to rank 4 (no rank 3)
SELECT name, score,
       RANK() OVER (ORDER BY score DESC) AS rnk
FROM players;

name  | score | rnk
------+-------+----
 Ali  |    95 |   1
 Sara |    90 |   2
 Omar |    90 |   2
 Nour |    72 |   4

RANK vs DENSE_RANK vs ROW_NUMBER on the same tied data

-- Same ORDER BY, three functions.
-- RANK leaves a gap (1,1,3), DENSE_RANK does not (1,1,2),
-- ROW_NUMBER is always unique (1,2,3).
SELECT name, score,
       RANK()       OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM players;

name  | score | rnk | dense_rnk | row_num
------+-------+-----+-----------+--------
 Ali  |    95 |   1 |         1 |       1
 Sara |    90 |   2 |         2 |       2
 Omar |    90 |   2 |         2 |       3
 Nour |    72 |   4 |         3 |       4

Rank within groups using PARTITION BY

-- Rank each employee inside their own department;
-- the rank restarts at 1 for every department
SELECT department, name, salary,
       RANK() OVER (PARTITION BY department
                    ORDER BY salary DESC) AS dept_rank
FROM employees;

department | name  | salary | dept_rank
-----------+-------+--------+----------
 Sales     | Ali   |   9000 |         1
 Sales     | Omar  |   7000 |         2
 Sales     | Nour  |   7000 |         2
 IT        | Sara  |   8500 |         1
 IT        | Zaid  |   6000 |         2

Nth highest value with RANK in a CTE

-- Find the employees with the 3rd highest salary,
-- including ties, using RANK inside a CTE
WITH ranked AS (
  SELECT name, salary,
         RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, salary
FROM ranked
WHERE rnk = 3;

name | salary
-----+-------
 Ola |   7000
 Dana |  7000

Common mistakes

-- Expecting a gapless 1, 2, 3 sequence.
-- With ties this returns 1, 2, 2, 4 (a gap at 3).
SELECT name, score,
       RANK() OVER (ORDER BY score DESC) AS rnk
FROM players;

-- Use DENSE_RANK when you want no gaps: 1, 2, 2, 3
SELECT name, score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS rnk
FROM players;

-- Using RANK to get a unique row number, then being
-- surprised two rows share rank 2 and none is rank 3
SELECT name, score,
       RANK() OVER (ORDER BY score DESC) AS pick
FROM players;

-- ROW_NUMBER always gives a unique number per row
SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS pick
FROM players;

-- No ORDER BY inside OVER: RANK has nothing to rank by
SELECT name, score,
       RANK() OVER () AS rnk
FROM players;

-- ORDER BY inside OVER is required for RANK
SELECT name, score,
       RANK() OVER (ORDER BY score DESC) AS rnk
FROM players;

Performance

Like every ranking window function, RANK() requires the rows to be sorted by the ORDER BY (and grouped by PARTITION BY) before it can assign numbers. That sort is the main cost: if the database cannot read the rows in the needed order from an index, it performs an explicit sort, which uses memory and can spill to disk on large inputs.

A composite index that matches the PARTITION BY columns followed by the ORDER BY columns lets the engine feed rows to the window operator already ordered, avoiding a separate sort. See the indexing guide for how ordered index scans remove sort steps.

RANK(), DENSE_RANK() and ROW_NUMBER() share the same window definition and cost the same to compute, so pick the one with the correct tie behaviour rather than choosing on performance. To fetch only the top N per group, rank in a subquery or CTE and filter on the rank in the outer query, since the window function cannot appear directly in a WHERE clause.

Interview questions

SELECT name, score,
       RANK()       OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM players;

WITH ranked AS (
  SELECT name, salary,
         RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, salary
FROM ranked
WHERE rnk = 3;  -- 3rd highest salary, including ties

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