Shape / rows · All operations

Keep the first rows of each group

A variant of Take the first or last rows

Keep the first, last or top N rows within every group rather than across the table.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep the first, last or top N rows of each group

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
-- the first and the last order of each product, by date
CREATE TEMP TABLE first_order AS
SELECT product, order_id FROM orders
QUALIFY row_number() OVER (PARTITION BY product ORDER BY order_date, order_id) = 1;
CREATE TEMP TABLE last_order AS
SELECT product, order_id FROM orders
QUALIFY row_number() OVER (PARTITION BY product ORDER BY order_date DESC, order_id DESC) = 1;
-- the two largest orders of each product
CREATE TEMP TABLE top_two AS
SELECT product, order_id, amount FROM orders
QUALIFY row_number() OVER (PARTITION BY product ORDER BY amount DESC NULLS LAST, order_id) <= 2;
SELECT * FROM top_two ORDER BY product, amount DESC;
SELECT CASE WHEN ((SELECT map_from_entries(list((product, order_id) ORDER BY product)) FROM first_order) = MAP {'gadget': 2, 'gizmo': 4, 'widget': 1} AND (SELECT map_from_entries(list((product, order_id) ORDER BY product)) FROM last_order) = MAP {'gadget': 9, 'gizmo': 10, 'widget': 8} AND (SELECT list(order_id ORDER BY order_id) FROM top_two) = [2, 5, 6, 7, 8, 10] AND (SELECT max(n) FROM (SELECT count(*) AS n FROM top_two GROUP BY product)) = 2) THEN 'as expected' ELSE error('the first, last and two largest orders of each product were not the expected orders') 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 first, last and two largest orders of each product were not the expected orders”.
  • 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).

Duckle (1)
  • Top N per Groupread 2026-09-25

    keep the top N rows per group by a row_number window

Polars (2)
Talend (1)
  • tTopByread 2026-09-27

    Group rows, sort within each group and keep a given number of first rows per group

Background

In DuckDB, a top-N in group query computes the top N rows in each group, ordered by some criteria.[1] DuckDB also provides aggregates such as max(arg, n) and arg_max(arg, val, n) that return the top n rows in a group based on a specific column.[1] In PostgreSQL, row_number returns the number of the current row within its partition, counting from 1.[2]

When to use it

Reach for this when the question is per group: the latest order of each customer, the two largest sales in each region, the first reading of each day. (Meridian's view)

DuckDB's QUALIFY clause filters on the results of window functions, avoiding the need for a subquery or WITH clause.[3]

ROW_NUMBER can be non-deterministic: when the sort key is not unique, rows sharing a value may be numbered differently each time the query runs.[4] End the window's ORDER BY with a column that is unique within each group, so every group keeps the same rows on every run. (Meridian's view) For the last rows of each group, reverse the window's ORDER BY, so the newest or largest rows are numbered 1. (Meridian's view)

In DuckDB, window functions are blocking operators that require their entire input to be buffered, making them one of the most memory-intensive operators in SQL.[5]

The method

Window functions were incorporated into the SQL:2003 standard.[6] In a window function, the PARTITION BY clause groups rows into partitions and the function is applied to each partition separately.[6] When PARTITION BY is omitted, as with an empty OVER() clause, the entire result set is treated as a single partition.[6]

In DuckDB, QUALIFY comes after the WINDOW clause and before ORDER BY in a SELECT statement.[3] In DuckDB, QUALIFY can filter on a window function written in the QUALIFY clause itself or on one defined and named in the SELECT clause.[3]

Compared with the alternatives

In PostgreSQL, rank returns the row_number of the first row in its peer group, leaving gaps, and dense_rank returns a rank without gaps, effectively counting peer groups.[2]

Sources

  1. [1]Friendly SQL – DuckDBdocumentation
  2. [2]PostgreSQL: Documentation: 18: 9.22. Window Functionsdocumentation
  3. [3]QUALIFY Clause – DuckDBdocumentation
  4. [4]Select (SQL)secondary source
  5. [5]Window Functions – DuckDBdocumentation
  6. [6]Window function (SQL)secondary source