Shape / rows · All operations

Number or rank rows

Row number, record id, rank, dense rank, ntile.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
several clauses of SQL, or a window

The DuckDB step

Row number, rank and quartile

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT order_id, amount,
       row_number() OVER (ORDER BY order_id) AS rn,
       dense_rank() OVER (ORDER BY amount DESC NULLS LAST) AS amount_rank,
       ntile(4) OVER (ORDER BY amount) AS quartile
FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) FILTER (WHERE amount = 510 AND amount_rank = 1) = 2 AND max(rn) = 10 AND count(DISTINCT quartile) = 4 FROM result)) THEN 'as expected' ELSE error('the two largest orders do not share rank 1, or the numbering is wrong') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the two largest orders do not share rank 1, or the numbering is wrong”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

  • Split rows into equal buckets

    Assign each row to one of N buckets of equal size, in order.

  • Percentile rank

    Give each row its relative position within its partition as a fraction from 0 to 1.

  • Rank rows

    Add a column giving each row its rank by one or more ordering columns, with ties sharing a rank.

  • Number runs of equal values

    Add a number that restarts, or an id that changes, whenever the value of chosen columns changes from one row to the next.

  • Add a unique id

    Add a column holding a random, hashed or time-ordered identifier that is unique to each row and does not depend on row order.

Flag the last row

Add a flag that is true only on the last row of the stream.

Find the position of a row

Return the row positions at which given rows occur in a table.

Number rows in order

Add a column counting the rows from a start value, in their order or within groups, from a counter or a sequence.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (2)
  • Rankread 2026-09-27

    Add rank columns of several ranking types, ordered and optionally grouped by columns

  • Record IDread 2026-09-27

    Add a sequential unique identifier column, numeric or string

Apache DataFusion (6)
  • cume_distread 2026-09-27

    Window: Relative rank: rows preceding or peer with the current row over total rows

  • dense_rankread 2026-09-27

    Window: Rank of the current row without gaps

  • ntileread 2026-09-27

    Window: Bucket number from 1 to n dividing the partition as equally as possible

  • percent_rankread 2026-09-27

    Window: Percentage rank of the current row within its partition

  • rankread 2026-09-27

    Window: Rank of the current row with gaps for ties

  • row_numberread 2026-09-27

    Window: Number of the current row within its partition, from 1

Apache Hop (4)
AWS Glue (2)
  • Identifierread 2026-09-27

    Add a column holding a numeric identifier per row

  • UUIDread 2026-09-27

    Add a column holding a universally unique identifier per row

Azure Data Factory (2)
  • Rankread 2026-09-27

    Add a rank column ordered by sort conditions

  • Surrogate keyread 2026-09-27

    Add an incrementing key column that carries no business meaning

Bruin (1)
  • rankread 2026-09-27

    SQL RANK() over the window

DuckDB (8)
Duckle (6)
  • Dense Rankread 2026-09-25

    DENSE_RANK over a window

  • NTileread 2026-09-25

    NTILE bucket over a window

  • Rankread 2026-09-25

    RANK over a window

  • Row Numberread 2026-09-25

    ROW_NUMBER over a window

  • Surrogate Keyread 2026-09-25

    add a dimension key: md5 of the natural key, or a sequence

  • UUIDread 2026-09-25

    add a UUID v4 column per row

EasyMorph (3)
Ibis (8)
  • CumeDistread 2026-09-27

    Cumulative-distribution position of each row within its window

  • DenseRankread 2026-09-27

    Rank rows in an ordered partition; ties share a rank with no gaps

  • MinRankread 2026-09-27

    Rank rows in an ordered partition; ties share a rank and leave gaps

  • NTileread 2026-09-27

    Assign each row of an ordered window to one of N equal-sized buckets

  • PercentRankread 2026-09-27

    Relative rank of each row within its window, as a fraction

  • RandomUUIDread 2026-09-27

    Generate a random UUID

  • RowIDread 2026-09-27

    Expose the backend's row identifier as a column

  • RowNumberread 2026-09-27

    Number the rows of a window, counting from 0

KNIME Analytics Platform (2)
  • Rankread 2026-09-27

    Rank rows by ordering columns, optionally within groups, with a chosen tie mode

  • RowIDread 2026-09-27

    Replace the row identifier with a generated or column-derived one, or copy it to a column

Pentaho Data Integration (4)
Polars (5)
  • DataFrame.with_row_countread 2026-09-27

    Add a row counter as the first column; deprecated in favour of with_row_index

  • DataFrame.with_row_indexread 2026-09-27

    Add a row index as the first column, starting from an offset

  • Expr.rankread 2026-09-27

    Rank the values of a column; ties by average, min, max, dense, ordinal or random

  • Expr.rle_idread 2026-09-27

    Give every run of identical consecutive values its own integer id

  • row_indexread 2026-09-27

    A running integer index for the rows in the current context

Power Query M (4)
  • Table.AddIndexColumnread 2026-09-27

    Add a row-position column with a chosen start value and increment

  • Table.AddRankColumnread 2026-09-27

    Add a rank column ordered by one or more columns, with a choice of tie handling

  • Table.PositionOfread 2026-09-27

    Return the row position, or positions, at which a given row occurs

  • Table.PositionOfAnyread 2026-09-27

    Return the row positions at which any of the given rows occurs

RapidMiner Studio (3)
  • Generate Batchread 2026-09-27

    Add a batch-number column dividing rows into equally sized batches

  • Generate IDread 2026-09-27

    Add an incrementing ID column to the rows

  • Generate Rank (In Database)read 2026-09-27

    Add a rank or dense rank column within a partition, executed in the database

Tableau Prep (7)
  • RANKread 2026-09-27

    Standard competition rank within a partition; ties share a rank and the next rank skips

  • RANK_DENSEread 2026-09-27

    Dense rank within a partition; ties share a rank and no rank is skipped

  • RANK_MODIFIEDread 2026-09-27

    Modified competition rank within a partition; ties take the last position's rank

  • RANK_PERCENTILEread 2026-09-27

    Percentile rank from 0 to 1 within a partition

  • ROW_NUMBERread 2026-09-27

    Sequential row id within a partition by an order

  • Rankread 2026-09-27

    Rank rows by ordered fields within optional groups

  • Source Row Numberread 2026-09-27

    Include a generated field holding each row's position in the Excel or text source

Trifacta (4)
  • DENSERANK Functionread 2026-09-27

    Rank within ordered groups; ties share a rank and no rank is skipped

  • RANK Functionread 2026-09-27

    Rank within ordered groups; ties share a rank and the next rank skips

  • ROWNUMBER Functionread 2026-09-27

    Row number by an order, optionally restarting per group

  • sourcerownumberread 2026-09-27

    New column holding each row's number in the original source

VisiData (7)
  • addcol-incrread 2026-09-23

    Add column of incrementing integers

  • addcol-incr-keyread 2026-09-23

    Add running number that restarts whenever the key column values change

  • addcol-incr-stepread 2026-09-23

    Add column incrementing by a given step

  • addcol-rank-sheetread 2026-09-23

    Add column ranking each row by its key columns

  • addcol-rownumread 2026-09-23

    (experimental) Add column holding the original row order

  • setcol-incrread 2026-09-23

    Set current column for selected rows to incrementing integers

  • setcol-incr-stepread 2026-09-23

    Set current column for selected rows to values incrementing by a given step

Background

In PostgreSQL and DuckDB, row_number gives each row its number within its partition, counting from 1.[1][2] In SQL, a window function uses values from one or more rows to return a value for each row, where an aggregate returns one value for many rows.[3] In computer data processing, ordinal ranking, in which every item receives a distinct number, is also referred to as row numbering.[4]

When to use it

In PostgreSQL, row_number numbers the rows of each partition in the order the window's ORDER BY defines, with tied rows numbered in an unspecified order.[5] In SQL Server, ROW_NUMBER is nondeterministic.[6] For ordinal ranking, a system that is arbitrary but consistent, such as adding other attributes to the ranking order, is generally preferable, as it gives stable results when the ranking is repeated.[4] Give the ordering a column that is unique, such as an id, whenever the same row must get the same number on every run. (Meridian's view)

In DuckDB, window functions with an empty OVER clause preserve the original row order, and row_number() OVER () turns that order into an explicit column.[7] In DuckDB, JOIN, GROUP BY and ORDER BY do not guarantee that row order is preserved.[7] Number the rows before any join or sort if the order a file arrived in is something you want to keep. (Meridian's view)

In PostgreSQL, a row removed because it does not meet the WHERE condition is not seen by any window function.[5] In PostgreSQL, window functions are permitted only in the SELECT list and the ORDER BY clause, and a sub-select can filter rows on their result.[5] DuckDB's QUALIFY clause filters the results of window functions without a subquery, much as HAVING filters the results of aggregate functions.[8] Filter first when the numbers should count only the rows that remain, and number first, in a subquery, when each row should keep its place in the whole table. (Meridian's view)

The method

Window functions were incorporated into the SQL:2003 standard.[3][2] In PostgreSQL, the PARTITION BY clause within OVER divides the rows into partitions, and ORDER BY within OVER controls the order in which window functions process them.[5] In PostgreSQL, when PARTITION BY is omitted, there is a single partition containing all rows.[5] In PostgreSQL, the window ORDER BY does not have to match the order in which the rows are output.[5]

In PostgreSQL, several window functions can share one window definition by naming it in a WINDOW clause and referring to it in OVER.[5]

Compared with the alternatives

In DuckDB, the rowid pseudo-column of a materialised table can be used, like row_number() OVER (), to make the original row order an explicit column.[7] Reach for a row number when every row needs its own number, a rank when tied rows should share one, a bucket when rows should fall into a fixed number of groups, and a UUID or a hash when the id must not depend on order at all. (Meridian's view)

Sources

  1. [1]PostgreSQL: Documentation: 18: 9.22. Window Functionsdocumentation
  2. [2]Window Functions – DuckDBdocumentation
  3. [3]Window function (SQL) - Wikipediasecondary source
  4. [4]Ranking - Wikipediasecondary source
  5. [5]PostgreSQL: Documentation: 18: 3.5. Window Functionsdocumentation
  6. [6]ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learndocumentation
  7. [7]Order Preservation – DuckDBdocumentation
  8. [8]QUALIFY Clause – DuckDBdocumentation

Used in

One Protocol uses this operation: