Shape / rows · All operations

Rank rows

A variant of Number or rank rows

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

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Rank rows by one or more columns, with ties sharing a rank

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 ranked AS
SELECT order_id, region, amount,
       rank() OVER (ORDER BY amount DESC NULLS LAST) AS by_amount,
       dense_rank() OVER (ORDER BY amount DESC NULLS LAST) AS by_amount_dense,
       rank() OVER (ORDER BY region NULLS LAST, amount DESC NULLS LAST) AS by_region_amount
FROM orders;
SELECT * FROM ranked ORDER BY order_id;
SELECT CASE WHEN ((SELECT list(by_amount ORDER BY order_id) FROM ranked) = [5, 7, 3, 10, 8, 1, 6, 1, 9, 4] AND (SELECT list(by_amount_dense ORDER BY order_id) FROM ranked) = [4, 6, 2, 9, 7, 1, 5, 1, 8, 3] AND (SELECT list(by_region_amount ORDER BY order_id) FROM ranked) = [4, 5, 6, 2, 7, 8, 1, 8, 10, 3]) THEN 'as expected' ELSE error('the ranks did not give the two 510.00 orders the same rank, or did not order ties on a second column') 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 ranks did not give the two 510.00 orders the same rank, or did not order ties on a second column”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

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

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

Apache DataFusion (2)
  • dense_rankread 2026-09-27

    Window: Rank of the current row without gaps

  • rankread 2026-09-27

    Window: Rank of the current row with gaps for ties

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

    Add a rank column ordered by sort conditions

Bruin (1)
  • rankread 2026-09-27

    SQL RANK() over the window

DuckDB (3)
  • dense_rankread 2026-09-27

    dense_rank aggregate

  • rankread 2026-09-27

    rank aggregate

  • rank_denseread 2026-09-27

    rank_dense aggregate

Duckle (2)
  • Dense Rankread 2026-09-25

    DENSE_RANK over a window

  • Rankread 2026-09-25

    RANK over a window

EasyMorph (1)
  • Enumerate groupsread 2026-09-27

    Number each distinct combination of values in given columns

Ibis (2)
  • 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

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

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

Polars (1)
  • Expr.rankread 2026-09-27

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

Power Query M (1)
  • Table.AddRankColumnread 2026-09-27

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

RapidMiner Studio (1)
Tableau Prep (4)
  • 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

  • Rankread 2026-09-27

    Rank rows by ordered fields within optional groups

Trifacta (2)
  • 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

VisiData (1)

Background

A ranking orders a set of items so that each is ranked higher than, lower than or equal to another, and two items with the same rank are a tie.[1] In standard competition ranking, items that compare equal receive the same ranking number, and then a gap is left in the ranking numbers.[1] In dense ranking, items that compare equal receive the same ranking number, and the next items receive the immediately following number.[1] In PostgreSQL and DuckDB, the rank function gives the rank with gaps, and the dense_rank function gives the rank without gaps.[2][3]

When to use it

In standard competition ranking, each item's ranking number is 1 plus the number of items ranked above it.[1] Standard competition ranking is frequently adopted for competitions, as a tie leaves the position of all those ranked below it unaffected.[1] In dense ranking, each item's ranking number is 1 plus the number of items ranked above it that are distinct with respect to the ranking order.[1] Use a competition rank when the number should say how many rows came ahead of this one, and a dense rank when it should say which distinct value this is, such as the third-highest price. (Meridian's view)

In PostgreSQL, when neither NULLS FIRST nor NULLS LAST is given, nulls sort last in ascending order and first in descending order, as though nulls were larger than non-nulls.[4] Write NULLS FIRST or NULLS LAST into the ordering a rank uses, so that where missing values land does not depend on which engine runs the query. (Meridian's view)

In pandas, the rank method by default assigns equal values a rank that is the average of the ranks of those values.[5] In Polars, the rank expression's default method is average, and its min method is also referred to as competition ranking.[6] In Polars, rank ranks only non-null values and preserves the null ones, though a reader coming from SQL may expect null values to be ranked last.[6] Check which tie rule and which treatment of missing values a tool's rank uses before comparing its numbers with another tool's. (Meridian's view)

The method

In SQL, a window function uses values from one or multiple rows to return a value for each row, where an aggregate function returns a single value for multiple rows.[7] In PostgreSQL, the PARTITION BY clause within a window function's OVER clause divides the rows into partitions, and when it is omitted there is a single partition containing all rows.[8] In PostgreSQL, the ranking window functions depend on the sort ordering given by the ORDER BY clause of their window definition.[2] In PostgreSQL, rows that are not distinct when considering only the ORDER BY columns are said to be peers, and the ranking functions give the same answer for all rows of a peer group.[2] In PostgreSQL and DuckDB, rank gives the row_number of the first row in the current row's peer group, and dense_rank effectively counts peer groups.[2][3] In SQL Server, if the two top salespeople have the same sales value, RANK ranks both one and the next salesperson three, while DENSE_RANK ranks the next salesperson two.[9][10]

In KNIME's Rank node, when several ordering criteria are set, the table is first sorted by the primary criterion, and later criteria are used only in the case of ties.[11] In KNIME's Rank node, ranking can be done within groups, where each unique combination of values in the chosen category columns defines a separate ranking group.[11]

In ordinal ranking, all items receive distinct ordinal numbers, including items that compare equal.[1] In modified competition ranking, the gap in the ranking numbers is left before the set of equal items rather than after it, so each item's number equals the number of items ranked equal to it or above it.[1] In fractional ranking, items that compare equal receive the same ranking number, which is the mean of what they would have under ordinal ranking.[1] Fractional ranking keeps the sum of the ranking numbers the same as under ordinal ranking, and for that reason it is used in statistical tests.[1] Four items, with the first ranked ahead of the second and third, which compare equal and are both ranked ahead of the fourth, are ranked 1224 by standard competition ranking, 1334 by modified competition ranking, 1223 by dense ranking and 1 2.5 2.5 4 by fractional ranking.[1]

Compared with the alternatives

In SQL Server, ROW_NUMBER numbers all rows sequentially, for example 1, 2, 3, 4, 5, while RANK provides the same numeric value for ties, for example 1, 2, 2, 4, 5.[9] Choose a rank over a row number whenever tied rows deserve the same place, and a row number only when every row must have its own. (Meridian's view)

Sources

  1. [1]Ranking - Wikipediasecondary source
  2. [2]PostgreSQL: Documentation: 18: 9.22. Window Functionsdocumentation
  3. [3]Window Functions – DuckDBdocumentation
  4. [4]PostgreSQL: Documentation: 18: SELECTdocumentation
  5. [5]pandas.Series.rank — pandas 3.1.1 documentationdocumentation
  6. [6]polars.Expr.rank — Polars documentationdocumentation
  7. [7]Window function (SQL) - Wikipediasecondary source
  8. [8]PostgreSQL: Documentation: 18: 3.5. Window Functionsdocumentation
  9. [9]RANK (Transact-SQL) - SQL Server | Microsoft Learndocumentation
  10. [10]DENSE_RANK (Transact-SQL) - SQL Server | Microsoft Learndocumentation
  11. [11]Rankdocumentation