---
title: "ROW_NUMBER"
description: "ROW_NUMBER() is a SQL window function that assigns a unique, sequential integer to each row within a partition based on a specified order, with no ties."
canonical: "https://motherduck.com/glossary/row-number/"
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: "SQL Golf: 5 SQL Tricks You Should (Probably) Never Use"
    url: "https://motherduck.com/blog/its-a-sql-golf-quackmas/"
---

# ROW_NUMBER

> ROW_NUMBER() is a SQL window function that assigns a unique, sequential integer to each row within a partition based on a specified order, with no ties.

## Overview
`ROW_NUMBER()` is a window function that numbers the rows returned by a query, starting at 1, according to the ordering specified in its `OVER` clause. Unlike `RANK()` or `DENSE_RANK()`, it never produces ties — even rows with identical values in the `ORDER BY` expression get distinct, arbitrary-but-deterministic numbers based on row order.

```sql
SELECT
  customer_id,
  order_date,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date DESC
  ) AS rn
FROM orders;
```

## Common uses
- **Deduplication**: keep only the first row per group (e.g., the latest order per customer) by filtering `rn = 1`.
- **Pagination**: number all rows in a result set and slice a page with a range filter.
- **Top-N per group**: combine with `PARTITION BY` to get, say, the top 3 highest-value orders per customer.

A classic dedup pattern:

```sql
SELECT * EXCLUDE (rn)
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
WHERE rn = 1;
```

## DuckDB specifics
DuckDB supports the full window function syntax needed for `ROW_NUMBER()`, and it also supports `QUALIFY`, which removes the need for a wrapping subquery entirely:

```sql
SELECT * EXCLUDE (rn)
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders)
QUALIFY rn = 1;
```

Because `ROW_NUMBER()` requires no arguments — only an `OVER` clause — it's one of the simplest window functions to reach for whenever you need stable, unique row identifiers within groups, without altering the underlying table.