Shape / rows · All operations

Take the first or last rows

First N, last N, top N, row range, skip rows.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
fewer rows, same columns
Level
one clause of SQL

The DuckDB step

Top two orders per region

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 * FROM orders QUALIFY row_number() OVER (PARTITION BY region ORDER BY amount DESC NULLS LAST) <= 2;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 9 AND count(*) FILTER (WHERE order_id = 2) = 0 FROM result) AND (SELECT max(n) <= 2 FROM (SELECT count(*) AS n FROM result GROUP BY region))) THEN 'as expected' ELSE error('a region kept more than two orders, or order 2 survived') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “a region kept more than two orders, or order 2 survived”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Keep or skip the first or last rows

Keep the first or last N rows, skip some, or keep one contiguous run by offset and count.

Keep every nth row

Keep or drop rows at a regular interval, such as every third row.

Trim binary content

Discard a byte range at the start or end of binary content.

Take the first row as a value

Return one row as a record, or one cell as a value, instead of a table.

Keep rows up to a condition

Keep or drop the rows before or after the first row that meets a condition.

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 (3)
  • Sampleread 2026-09-27

    Keep first N, last N, skip N, 1 of every N, 1-in-N chance or first N percent, optionally per group

  • Sample In-DBread 2026-09-27

    Limit an in-DB stream to a number or percentage of records, optionally by sort order

  • Select Recordsread 2026-09-27

    Return records by position, including discontinuous ranges

Apache DataFusion (4)
  • LIMITread 2026-09-27

    Pipe: restrict row count, BigQuery-style |> syntax

  • Limit (LIMIT)read 2026-09-27

    Restrict the number of rows returned

  • OFFSETread 2026-09-27

    Skip a number of rows before returning results

  • limitread 2026-09-27

    DataFrame method: Skip and fetch a limited number of rows

Apache Hop (1)
  • Sample Rowsread 2026-09-27

    Keep rows whose line numbers fall in listed ranges

Apache NiFi (1)
  • ModifyBytesread 2026-09-27

    Discard a byte range at the start and end of binary content, or all of it

Azure Data Factory (1)
  • Reducing Rowsread 2026-09-27

    Keep or remove top rows and keep a range, by count: Table.FirstN, Table.Skip, Table.Range, …

DuckDB (1)
Duckle (3)
EasyMorph (3)
  • Keep min/maxread 2026-09-27

    Keep or remove the rows holding the N largest or smallest values of a column

  • Trim tableread 2026-09-27

    Keep or remove the first or last N rows

  • Trim table by conditionread 2026-09-27

    Keep or remove the rows above or below the first row that meets a condition

Ibis (1)
  • Limitread 2026-09-27

    Keep N rows after skipping an offset

KNIME Analytics Platform (4)
  • Cell Extractorread 2026-09-27

    Extract one cell by row and column as a 1x1 table and a flow variable

  • Table Cropperread 2026-09-27

    Crop a table to a range of rows and a range of columns

  • Table Splitterread 2026-09-27

    Split a table in two at the first row matching a condition

  • Top k Row Filterread 2026-09-27

    Keep the top k rows under a user-defined sort order

Pentaho Data Integration (1)
  • Sample rowsread 2026-09-27

    Keep rows by their line number

Polars (12)
Power Query M (16)
  • Table.AlternateRowsread 2026-09-27

    Keep rows in a repeating take-then-skip pattern after an initial offset

  • Table.Firstread 2026-09-27

    Return the first row as a record, or a default when the table is empty

  • Table.FirstNread 2026-09-27

    Keep the first N rows, or leading rows while a condition holds

  • Table.FirstValueread 2026-09-27

    Return the value in the first column of the first row, or a default

  • Table.Lastread 2026-09-27

    Return the last row as a record, or a default when the table is empty

  • Table.LastNread 2026-09-27

    Keep the last N rows, or trailing rows while a condition holds

  • Table.Maxread 2026-09-27

    Return the row with the largest value under the comparison criteria, or a default

  • Table.MaxNread 2026-09-27

    Keep the N largest rows under the comparison criteria, or rows while a condition holds

  • Table.Minread 2026-09-27

    Return the row with the smallest value under the comparison criteria, or a default

  • Table.MinNread 2026-09-27

    Keep the N smallest rows under the comparison criteria, or rows while a condition holds

  • Table.Rangeread 2026-09-27

    Keep a run of rows starting at an offset, optionally limited to a count

  • Table.RemoveFirstNread 2026-09-27

    Drop the first N rows, or leading rows while a condition holds

  • Table.RemoveLastNread 2026-09-27

    Drop the last N rows, or trailing rows while a condition holds

  • Table.RemoveRowsread 2026-09-27

    Drop a count of rows starting at a given offset

  • Table.SingleRowread 2026-09-27

    Return the one row of a one-row table as a record; error otherwise

  • Table.Skipread 2026-09-27

    Skip the first N rows, or leading rows while a condition holds

RapidMiner Studio (2)
SQLMesh (1)
  • @LIMITread 2026-09-27

    Include a LIMIT clause only when the argument is true

Tableau Prep (1)
Talend (3)
  • tSampleRowread 2026-09-27

    Keep rows by line number, given as a list of single lines and groups of lines

  • tTopread 2026-09-27

    Sort rows and keep a given number of first rows

  • tTopByread 2026-09-27

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

Trifacta (3)
  • Filter at intervalread 2026-09-27

    Keep or delete rows at a regular interval, every nth row

  • Filter from topread 2026-09-27

    Keep or delete a number of rows from the top

  • Filter rangeread 2026-09-27

    Keep or delete rows within a range of row positions

VisiData (2)
  • dup-limitread 2026-09-23

    (vdsql) Open duplicate of the selected rows with a given row limit

  • dup-nolimitread 2026-09-23

    (vdsql) Open duplicate of the selected rows with no row limit

Background

Limiting result rows sets a maximum number of rows that a query returns, and the way to write it often varies from one database vendor to another.[1] Without an ORDER BY clause, the order of rows returned by an SQL query is undefined.[1] ISO SQL:2008 introduced the FETCH FIRST clause for limiting the rows a query returns.[1]

When to use it

A maximum number of rows can be used for testing, or to prevent consuming excessive resources when a query returns more than expected.[1] In DuckDB, LIMIT can be used without an ORDER BY, though the results might then not be deterministic, which can still be useful for a quick snapshot of the data.[2]

In PostgreSQL, a LIMIT without an ORDER BY that constrains the rows into a unique order returns an unpredictable subset of the query's rows.[3][4] PostgreSQL's query planner takes LIMIT into account, so different LIMIT and OFFSET values are very likely to give different plans and row orders, and inconsistent results unless ORDER BY enforces a predictable order.[3] In PostgreSQL, repeated runs of the same LIMIT query can even return different subsets of a table's rows when no ORDER BY enforces a deterministic subset.[4] Whenever the rows you keep matter, and not just their number, sort on columns that together are unique, so the same query keeps the same rows on every run. (Meridian's view)

In PostgreSQL, the rows skipped by an OFFSET still have to be computed inside the server, so a large OFFSET might be inefficient.[3] For a quick look at a table, a bare limit is fine; for anything you will report, reuse or page through, write the ORDER BY first and the limit second. (Meridian's view)

The method

In PostgreSQL, LIMIT gives the maximum number of rows to return and OFFSET the number of rows to skip, and when both appear the skipped rows are skipped before counting begins.[4][3] In SQLite, when a query would return fewer rows than the LIMIT without one, the entire result set is returned.[5] SQLite also accepts LIMIT with two expressions separated by a comma, where the first is the offset and the second the limit, the reverse of the OFFSET form.[5]

In DuckDB, LIMIT is an output modifier applied logically at the very end of the query, and it can also take a percentage, as in LIMIT 10%.[2] DuckDB introduced the SQL-standard FETCH FIRST ... ROWS ONLY syntax in DuckDB 2.0, as an alternative to LIMIT.[2] Some database systems offer non-standard syntax for limiting rows, such as LIMIT, TOP or FIRST, instead of or in addition to the standard syntax.[1]

The rows just before a given key value can be taken by filtering below it, sorting in reverse order, keeping the first rows, and sorting that result back into the original order.[1]

Compared with the alternatives

In ISO SQL:2003, result sets could be limited by using cursors, or by adding a window function to the SELECT statement.[1] A ROW_NUMBER window function ordered by a sort key and filtered to numbers of ten or fewer returns no more than ten rows.[1]

For paging through a very big dataset, rows can be filtered on a unique key greater than the last value of the previous page, and the first rows of what remains kept.[1] Prefer paging by a unique key over a growing offset when reading a large table page by page. (Meridian's view)

Sources

  1. [1]Select (SQL)secondary source
  2. [2]LIMIT and OFFSET Clauses – DuckDBdocumentation
  3. [3]PostgreSQL: Documentation: 18: 7.6. LIMIT and OFFSETdocumentation
  4. [4]PostgreSQL: Documentation: 18: SELECTdocumentation
  5. [5]SELECTdocumentation

Used in

One Protocol uses this operation: