Window Functions Ranking RANK · ties · gaps

RANK Window Function

Assign ranks within ordered partitions. Ties share a rank and the next rank is skipped — leaderboard-style. Pair with a CTE for top-N reports, partition by another column to rank within groups.

What RANK does

RANK() assigns a rank to each row within its partition, ordered by the ORDER BY clause. The first row gets rank 1, the next gets 2, and so on — but if two rows tie, they share the same rank, and the next rank skips ahead. So you can have ranks like 1, 2, 2, 4 with no rank 3.

It's the standard "leaderboard" ranking — the same way sports rankings handle ties.

Syntax

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

RANK() takes no arguments — the parens stay empty. The ORDER BY clause is required; without it, ranking has no meaning.

Demo dataset

SQL — Create & insert

CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    emp_name    VARCHAR(20) NOT NULL,
    emp_address VARCHAR(20) NOT NULL,
    emp_salary  INT          NOT NULL,
    date_of_joining DATE     NOT NULL
);

INSERT INTO employees VALUES
(1, 'Raj',    'Pune',   20000, '2020-01-01'),
(2, 'King',   'Pune',   20000, '2020-01-01'),
(3, 'Thomas', 'Mumbai', 35000, '2020-02-01'),
(4, 'Harold', 'Mumbai', 45000, '2020-02-01'),
(5, 'Steve',  'Mumbai', 45000, '2020-02-09'),
(6, 'Anita',  'Delhi',  38000, '2022-11-20');

Example 1 — rank by salary

SQL

SELECT emp_name,
       emp_salary,
       RANK() OVER (ORDER BY emp_salary DESC) AS salary_rank
FROM   employees;

Result

emp_name	emp_salary	salary_rank
Harold	45000	1
Steve	45000	1
Anita	38000	3
Thomas	35000	4
Raj	20000	5
King	20000	5

Notice the gaps. Harold and Steve both rank 1; the next employee gets rank 3 (not 2) because two rows already occupy the top spot. This "gap-after-tie" behaviour is what distinguishes RANK from DENSE_RANK.

Example 2 — rank within each city

SQL

SELECT emp_name,
       emp_address,
       emp_salary,
       RANK() OVER (PARTITION BY emp_address
                    ORDER BY emp_salary DESC) AS city_rank
FROM   employees
ORDER  BY emp_address, city_rank;

The rank resets at each new city — Mumbai's top earner gets rank 1, Pune's top earner gets rank 1 too, and so on.

Example 3 — top-N per group

The classic ranking use case. Find the top three earners overall — wrap the query in a CTE and filter on the rank.

SQL

WITH ranked AS (
    SELECT emp_name, emp_salary,
           RANK() OVER (ORDER BY emp_salary DESC) AS r
    FROM   employees
)
SELECT * FROM ranked WHERE r <= 3;

⚠ Filtering by rank can return more than N rows. If two rows tie at rank 3, "top 3 by RANK" still returns both because both are rank 3. If you need exactly N rows regardless of ties, use ROW_NUMBER() instead.

RANK vs DENSE_RANK vs ROW_NUMBER

Function	Tie behaviour	Example sequence
RANK	Ties share a rank, gaps after	1, 1, 3, 4
DENSE_RANK	Ties share a rank, no gaps	1, 1, 2, 3
ROW_NUMBER	Each row gets a unique number — ties broken arbitrarily	1, 2, 3, 4

Recap

RANK() assigns ranks within ordered partitions; ties share a rank.
After a tie, the next rank is skipped (1, 1, 3, …).
Add PARTITION BY to rank within groups.
For top-N reports, wrap in a CTE and filter — but be aware ties can push past N.
Use DENSE_RANK if you don't want gaps; ROW_NUMBER if you want unique numbers.