DENSE_RANK Window Function

Like RANK, but no gaps after ties. Sequence is always 1, 1, 2, 2, 3 — perfect for finding the Nth distinct value of a column when ties shouldn't shift the count.

DENSE_RANK() behaves like RANK() with one difference: when rows tie, the next rank is the very next integer, not a number that "skips" the tied rows. So a tie at rank 1 still produces rank 2 next, never rank 3.

It's "no gaps" ranking — useful when you want a stable count of distinct values, not a leaderboard position.

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');

SELECT emp_name,
       emp_salary,
       DENSE_RANK() OVER (ORDER BY emp_salary DESC) AS dense_rank
FROM   employees;

Compare with RANK() on the same data: there, Anita would be rank 3 because ranks 1 and 1 used "two slots." With DENSE_RANK(), Anita is rank 2 — no slot is wasted.

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

A canonical interview question. DENSE_RANK handles ties cleanly here: rank 2 is always the second distinct value, even if many people share the top.

WITH ranked AS (
    SELECT emp_name, emp_salary,
           DENSE_RANK() OVER (ORDER BY emp_salary DESC) AS dr
    FROM   employees
)
SELECT * FROM ranked WHERE dr = 2;

• DENSE_RANK() ranks ordered rows within partitions, never leaving gaps after ties.
• Sequence: 1, 1, 2, 2, 3, … — every distinct value gets a unique consecutive rank.
• Use it when you care about "Nth distinct value" rather than "Nth row."
• Use RANK for traditional leaderboard ranks; ROW_NUMBER for unique numbering.