---
title: "RANK and DENSE_RANK"
description: "RANK() and DENSE_RANK() are window functions that assign ranking numbers to rows within a partition, differing in how they handle ties and the gaps left afterward."
canonical: "https://motherduck.com/glossary/rank-and-dense-rank/"
related:
  - title: "Window functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/window-functions/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "EXPLAIN | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/explain/"
---

# RANK and DENSE_RANK

> RANK() and DENSE_RANK() are window functions that assign ranking numbers to rows within a partition, differing in how they handle ties and the gaps left afterward.

## Overview
Both `RANK()` and `DENSE_RANK()` assign an integer rank to each row within a partition based on an `ORDER BY` expression, and both give tied rows the same rank. They differ in what happens to the rank sequence after a tie:

- `RANK()` leaves a gap: if two rows tie for rank 1, the next row gets rank 3.
- `DENSE_RANK()` leaves no gap: if two rows tie for rank 1, the next row gets rank 2.

```sql
SELECT
  student,
  score,
  RANK() OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM exam_results;
```

Given scores `95, 95, 90, 80`, `RANK()` produces `1, 1, 3, 4` while `DENSE_RANK()` produces `1, 1, 2, 3`.

## When to use which
- Use `RANK()` for leaderboard-style results where the gap reflects "how many people beat you."
- Use `DENSE_RANK()` when you want a compact set of tiers or buckets, such as labeling "top 3 distinct price points" regardless of how many products share each price.

```sql
SELECT * EXCLUDE (dense_rnk)
FROM (
  SELECT product, price, DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rnk
  FROM products
)
WHERE dense_rnk <= 3;
```

## DuckDB specifics
DuckDB implements both functions per the SQL standard, requiring an `ORDER BY` inside the `OVER` clause (a `PARTITION BY` is optional). As with other window functions, DuckDB's `QUALIFY` clause lets you filter directly on the rank without a subquery:

```sql
SELECT product, price, DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rnk
FROM products
QUALIFY dense_rnk <= 3;
```

For a tie-free row count instead, use `ROW_NUMBER()`; for ranks expressed as a fraction between 0 and 1, use `PERCENT_RANK()` or `CUME_DIST()`.