row_number

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › 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.

Returns: BIGINT.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (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), 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. 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 'WINDOW' type=6 is currently not supported. Sort in a subquery instead (see What it does). To number rows per group, use Trino or Spark, or number the rows in your application.
Not in WHERE. WHERE row_number() OVER () > 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	`1` for every row: without `ORDER BY`, every row ties	Not usable without `ORDER BY`
`dense_rank()`, see window functions	`1` for every row	Not usable without `ORDER BY`
`rowid`, see 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()

[]
[]