Shape / rows · All operations

Keep rows by their positions

A variant of Take the first or last rows

Keep rows at listed line numbers or in several separate ranges of positions.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep rows at listed positions and in separate ranges of positions

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 numbered AS
SELECT row_number() OVER (ORDER BY order_id) AS pos, order_id, customer FROM orders;
-- listed positions
CREATE TEMP TABLE listed AS SELECT pos, order_id FROM numbered WHERE pos IN (2, 5, 9);
-- several separate ranges
CREATE TEMP TABLE ranges AS SELECT pos, order_id FROM numbered WHERE pos BETWEEN 1 AND 2 OR pos BETWEEN 8 AND 10;
-- one range, as a page
CREATE TEMP TABLE page AS SELECT pos, order_id FROM numbered ORDER BY pos LIMIT 3 OFFSET 4;
SELECT * FROM listed ORDER BY pos;
SELECT * FROM ranges ORDER BY pos;
SELECT CASE WHEN ((SELECT list(pos ORDER BY pos) FROM listed) = [2, 5, 9] AND (SELECT list(pos ORDER BY pos) FROM ranges) = [1, 2, 8, 9, 10] AND (SELECT list(pos ORDER BY pos) FROM page) = [5, 6, 7]) THEN 'as expected' ELSE error('the listed positions, the two ranges or the page were not the expected rows') 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 listed positions, the two ranges or the page were not the expected rows”.
  • 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)
  • Select Recordsread 2026-09-27

    Return records by position, including discontinuous ranges

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

    Keep rows whose line numbers fall in listed ranges

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

    Keep rows by their line number

Polars (1)
Talend (1)
  • tSampleRowread 2026-09-27

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

Background

In pandas, iloc selects rows by position, using purely integer-location based indexing.[1] In Apache Hop, the Sample Rows transform retains a sample set of rows, chosen by single line numbers or line number ranges.[2] In the relational model, the tuples of a relation appear in no particular order, and a similar comment applies to the rows of an SQL table.[3] In DuckDB, row_number() OVER () turns the original row order into an explicit column that other operations can refer to.[4]

When to use it

Reach for positions when they come from outside the data, such as a line number in an error report or a range someone has flagged in a file. (Meridian's view) When a column in the data identifies the rows you want, filter on that column instead, because it stays right when the rows are reordered. (Meridian's view)

In DuckDB, the CSV, JSON and Parquet readers preserve insertion order by default.[4] In DuckDB, joins, GROUP BY and UNION do not guarantee that row order is preserved.[4] Number the rows before any join or grouping, while their order is still the file's order, if the positions refer to lines of the file. (Meridian's view)

In pandas, iloc positions run from 0 to length-1 of the axis, while in PostgreSQL row_number counts from 1.[1][5] Check whether the positions you were given count from 0 or from 1 before you keep them; an off-by-one shift is easy to miss. (Meridian's view) In pandas, iloc raises an IndexError for an out-of-bounds integer position, while slice indexers allow out-of-bounds indexing.[1] In Polars, gather can generate a null row for an out-of-bounds index instead of raising an error, when null_on_oob is set.[6]

The method

In DuckDB, row_number gives the number of the current row within its partition, counting from 1, computed in the ORDER BY order when one is given.[7] On a materialised DuckDB table, the rowid pseudo-column can be used to the same effect as row_number() OVER ().[4] Number the rows once, in a column of their own, and select every listed position and range from that column, so they all refer to the same numbering. (Meridian's view)

In pandas, iloc accepts a single integer, a list of integers such as [4, 3, 0], or a slice of integers such as 1:7.[1] In Apache Hop, the Sample Rows transform takes a comma-separated list of single line numbers and line number ranges, such as 10,20,30,40,50..60.[2]

Compared with the alternatives

In SQLite, an OFFSET omits the first M rows of the result and returns the next N, where N is the value of the LIMIT.[8] A limit with an offset suits one run of rows; for several separate ranges or a scattered list of positions, filter on a numbered column instead of chaining queries. (Meridian's view)

Sources

  1. [1]pandas.DataFrame.iloc — pandas 3.1.1 documentationdocumentation
  2. [2]Sample Rows | Apache Hopdocumentation
  3. [3]Relation (database)secondary source
  4. [4]Order Preservation – DuckDBdocumentation
  5. [5]PostgreSQL: Documentation: 18: 9.22. Window Functionsdocumentation
  6. [6]polars.DataFrame.gather — Polars documentationdocumentation
  7. [7]Window Functions – DuckDBdocumentation
  8. [8]SELECTdocumentation