---
title: "row_number"
slug: "dev-database-reference-sql-functions-aggregate-row-number"
updated: 2026-10-30T18:18:46Z
published: 2026-10-30T18:18:46Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-aggregate-row-number"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# row_number

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate) › Window functions.* `row_number() OVER ()` numbers the rows of a result 1, 2, 3, and so on, in the order they reach it. To number the rows in an order you choose, sort them in a subquery first.

## Syntax

```
row_number() OVER ()
row_number() OVER w ... WINDOW w AS ()
```

`row_number` takes no arguments. Nothing goes inside `OVER ()` in the VAST Query Engine: see [Limitations and errors](/develop/docs/dev-database-reference-sql-functions-aggregate-row-number#limitations-and-errors).

**Returns:** `BIGINT`.

## Set up the examples

The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_aggregate_row_number`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the `run()` and `plan()` helpers, and creates a table of seven players' scores. Two players have the same score, and one has none:

```
import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_aggregate_row_number"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines (the table's row count, which can still be 0 just after an insert),
    with your database name shown as <bucket>."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line).replace(BUCKET, "<bucket>"))

TABLE = f"{SCHEMA}.scores"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, player VARCHAR, team VARCHAR, score BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Ada', 'red', 10), (2, 'Ben', 'red', -20), (3, 'Cho', 'blue', 0), "
    "(4, 'Dev', 'blue', 10), (5, 'Eve', 'green', 30), (6, 'Fay', 'green', 5), "
    "(7, 'Gus', 'green', NULL)")
```

```
[]
[]
[(7,)]
```

## What it does

`row_number()` gives the first row that reaches it the number 1, the next 2, and so on, without gaps or repeats, even when rows have the same values. It's computed after `WHERE`, so it numbers only the rows that pass.

With an empty `OVER ()`, the order is the order in which the rows arrive from the scan, which isn't defined: don't rely on it. To number the rows in a chosen order, sort them in a subquery with `ORDER BY ... LIMIT`, and number the subquery's rows:

```
SELECT row_number() OVER () AS rn, ... FROM (SELECT ... FROM table ORDER BY column LIMIT n)
```

`ORDER BY` inside `OVER (...)`, the usual way in other SQL dialects, isn't available in the VAST Query Engine.

## NULLs and edge cases

| Case | Result |
| --- | --- |
| Rows with the same values, or with NULLs | Each still gets its own number |
| A `WHERE` condition | Only the rows that pass are numbered, from 1 |
| No rows | No rows |
| No `FROM` (`SELECT row_number() OVER ()`) | `1` |
| A frame without `ORDER BY` or `PARTITION BY`, such as `OVER (ROWS UNBOUNDED PRECEDING)` | The same numbers as `OVER ()` |
| A named window, `OVER w ... WINDOW w AS ()` | The same numbers as `OVER ()` |
| An argument, `row_number(id)` | Error: `ROW_NUMBER needs 0 parameters, got 1` |

The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-aggregate-row-number#set-up-the-examples). The per-row cases number a subquery sorted by `id`, so the numbers don't depend on the arrival order:

```
run(f"SELECT count(*), min(rn), max(rn) FROM (SELECT row_number() OVER () AS rn FROM {TABLE})")
run(f"SELECT count(*), min(rn), max(rn) FROM (SELECT row_number() OVER () AS rn FROM {TABLE} WHERE score > 0)")
run(f"SELECT count(*) FROM (SELECT row_number() OVER () AS rn FROM {TABLE} WHERE id > 100)")
run("SELECT row_number() OVER ()")
run(f"SELECT id, row_number() OVER (), row_number() OVER (ROWS UNBOUNDED PRECEDING), row_number() OVER w "
    f"FROM (SELECT id FROM {TABLE} ORDER BY id LIMIT 100) WINDOW w AS ()")
run(f"SELECT row_number(id) OVER () FROM {TABLE}")
```

```
[(7, 1, 7)]
[(4, 1, 4)]
[(0,)]
[(1,)]
[(1, 1, 1, 1), (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, 4, 4), (5, 5, 5, 5), (6, 6, 6, 6), (7, 7, 7, 7)]
InternalError: Binder Error: ROW_NUMBER needs 0 parameters, got 1
```

## Examples

Number the players from the highest score to the lowest. The subquery sorts; `row_number()` numbers the sorted rows. Ada and Dev have the same score and still get different numbers:

```
run(f"SELECT row_number() OVER () AS position, player, score "
    f"FROM (SELECT player, score FROM {TABLE} ORDER BY score DESC NULLS LAST, id LIMIT 100)")
```

```
[(1, 'Eve', 30), (2, 'Ada', 10), (3, 'Dev', 10), (4, 'Fay', 5), (5, 'Cho', 0), (6, 'Ben', -20), (7, 'Gus', None)]
```

Keep the top three. A window function can't go in `WHERE`, so number the rows in a subquery and filter on the number outside it:

```
run(f"SELECT position, player FROM ("
    f"SELECT row_number() OVER () AS position, player "
    f"FROM (SELECT player FROM {TABLE} ORDER BY score DESC NULLS LAST, id LIMIT 100)) "
    f"WHERE position <= 3")
```

```
[(1, 'Eve'), (2, 'Ada'), (3, 'Dev')]
```

Number the players of one team, from 0. `WHERE` runs first, so the numbers start again for the rows that pass:

```
run(f"SELECT row_number() OVER () - 1 AS n, player "
    f"FROM (SELECT player FROM {TABLE} WHERE team = 'green' ORDER BY id LIMIT 100)")
```

```
[(0, 'Eve'), (1, 'Fay'), (2, 'Gus')]
```

## How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). `row_number() OVER ()` is a `STREAMING_WINDOW` step in the Query Engine, which numbers the rows it receives from the step below it. The plan shows it above the scan:

```
plan(f"SELECT id, row_number() OVER () FROM {TABLE}")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      STREAMING_WINDOW     │
│    ────────────────────   │
│        Projections:       │
│    ROW_NUMBER() OVER ()   │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

With a sorted subquery, a `TOP_N` step sorts the rows between the scan and the window step, so the window step numbers them in sorted order:

```
plan(f"SELECT id, row_number() OVER () FROM (SELECT id FROM {TABLE} ORDER BY id LIMIT 100)")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      STREAMING_WINDOW     │
│    ────────────────────   │
│        Projections:       │
│    ROW_NUMBER() OVER ()   │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           TOP_N           │
│    ────────────────────   │
│          Top: 100         │
│                           │
│         Order By:         │
│      vastdb."<bucket>     │
│/docs_sql_fn_aggregate_row_│
│   number".scores.id ASC   │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

`OVER (ORDER BY ...)` plans a `WINDOW` step instead, which the Query Engine doesn't run: the query fails when it runs. EXPLAIN shows the step:

```
plan(f"SELECT id, row_number() OVER (ORDER BY id) FROM {TABLE}")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           WINDOW          │
│    ────────────────────   │
│        Projections:       │
│  ROW_NUMBER() OVER (ORDER │
│    BY id ASC NULLS LAST)  │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Filter in `WHERE`.** A condition that pushes down is evaluated in VAST DataBase, so fewer rows reach the window step. Numbering happens after `WHERE` in any case.
- **Keep the `LIMIT` in the sorted subquery as small as you need.** The `TOP_N` step keeps only that many rows, and the window step numbers only those. The `LIMIT` must cover every row you want numbered: rows beyond it are left out.
- **Read only the columns you need.** The scan reads only the columns the query uses.

## Limitations and errors

- **No `ORDER BY` or `PARTITION BY` in `OVER (...)`.** `OVER (ORDER BY ...)`, `OVER (PARTITION BY ...)` and frames with either fail with `The operator &apos;WINDOW&apos; type=6 is currently not supported.` Sort in a subquery instead (see [What it does](/develop/docs/dev-database-reference-sql-functions-aggregate-row-number#what-it-does)). To number rows per group, use [Trino](/develop/docs/dev-database-integrations-trino) or [Spark](/develop/docs/dev-database-integrations-spark), or number the rows in your application.
- **Not in `WHERE`.** `WHERE row_number() OVER () &gt; 0` fails with `WHERE clause cannot contain window functions!`. Filter on the number in an outer query, or with `QUALIFY`.
- **No arguments.** `row_number(id) OVER ()` fails with `ROW_NUMBER needs 0 parameters, got 1`.

```
run(f"SELECT id, row_number() OVER (ORDER BY score) FROM {TABLE}")
run(f"SELECT id, row_number() OVER (PARTITION BY team) FROM {TABLE}")
run(f"SELECT id FROM {TABLE} WHERE row_number() OVER () > 0")
run(f"SELECT count(*) FROM (SELECT id FROM {TABLE} QUALIFY row_number() OVER () IS NOT NULL)")
```

```
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: Binder Error: WHERE clause cannot contain window functions!
[(7,)]
```

## Similar functions

| Function | With `OVER ()` | Rows with the same values |
| --- | --- | --- |
| `row_number()` | 1, 2, 3, ... | Different numbers |
| `rank()`, see [window functions](/develop/docs/dev-database-reference-sql-functions-aggregate-window-functions) | `1` for every row: without `ORDER BY`, every row ties | Not usable without `ORDER BY` |
| `dense_rank()`, see [window functions](/develop/docs/dev-database-reference-sql-functions-aggregate-window-functions) | `1` for every row | Not usable without `ORDER BY` |
| `rowid`, see [The rowid pseudo-column](/develop/docs/dev-database-reference-sql-select#the-rowid-pseudo-column) | Not a window function: the row's ID in the table, from 0 on a plain table | Different IDs |

The three window functions on the sorted players:

```
run(f"SELECT player, score, row_number() OVER (), rank() OVER (), dense_rank() OVER () "
    f"FROM (SELECT player, score FROM {TABLE} ORDER BY score DESC NULLS LAST, id LIMIT 100)")
```

```
[('Eve', 30, 1, 1, 1), ('Ada', 10, 2, 1, 1), ('Dev', 10, 3, 1, 1), ('Fay', 5, 4, 1, 1), ('Cho', 0, 5, 1, 1), ('Ben', -20, 6, 1, 1), ('Gus', None, 7, 1, 1)]
```

Clean up:

```
run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
```

```
[]
[]
```

## See also

- [Window functions](/develop/docs/dev-database-reference-sql-functions-aggregate-window-functions): the other window functions and which forms run.
- [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate).
- [SELECT](/develop/docs/dev-database-reference-sql-select), [EXPLAIN](/develop/docs/dev-database-reference-sql-explain) and [not supported](/develop/docs/dev-database-reference-sql-not-supported).