DENSE_RANK

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns the rank of the current row without gaps by counting peer groups within a partition.

1dense_rank() OVER (
2  [ PARTITION BY <expression> [, ...] ]
3  ORDER BY <expression> [, ...]
4)

For the full OVER clause and frame syntax, see Window Function Call Syntax. For details on naming a window definition with a WINDOW clause, see SELECT.

dense_rank() takes no arguments.

Returns a bigint.

• All window functions depend on the sort ordering specified by ORDER BY in the OVER clause.
• Rows that aren’t unique in the ORDER BY columns are peers. Ranking functions give the same answer to all peer rows.
• Unlike rank(), which leaves gaps in the rank sequence after ties (for example, 1, 2, 2, 4), dense_rank() assigns consecutive integers with no gaps (for example, 1, 2, 2, 3). It counts distinct peer groups rather than row positions.

Rank employees within each department by salary, from highest to lowest, without leaving gaps after ties.

1SELECT depname, empno, salary, dense_rank() OVER (PARTITION BY depname ORDER BY salary DESC)
2FROM empsalary;

For the develop department, salaries are 6000, 5200, 5200, 4500, 4200. dense_rank() produces 1, 2, 2, 3, 4 since the tie at 5200 doesn’t create a gap before the next rank. rank() on the same rows would produce 1, 2, 2, 4, 5, skipping rank 3 because two rows tied for rank 2.

• RANK
• MODIFIED_RANK
• ROW_NUMBER
• Aggregate Functions
• Window Functions