# Number runs of equal values

Shape / rows

A variant of [Number or rank rows](https://meridian.online/operations/number-or-rank-rows).

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

Page: <https://meridian.online/operations/number-or-rank-rows/number-runs-of-equal-values>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

number each run of equal values, restarting when the value changes

```sql
CREATE TEMP TABLE readings AS SELECT * FROM (VALUES
  (1, 'a', 'up'), (2, 'a', 'up'), (3, 'b', 'up'), (4, 'b', 'down'), (5, 'b', 'down'),
  (6, 'a', 'down'), (7, NULL, 'down'), (8, NULL, 'down'), (9, 'a', 'down')
) t(seq, station, status);
-- a new run starts at the first row and wherever station or status differs from the row before; NULLs equal NULLs
CREATE TEMP TABLE changes AS
SELECT *, (row_number() OVER w = 1 OR (station, status) IS DISTINCT FROM (lag(station) OVER w, lag(status) OVER w))::INT AS starts_run
FROM readings WINDOW w AS (ORDER BY seq);
CREATE TEMP TABLE result AS
SELECT seq, station, status, run_id, row_number() OVER (PARTITION BY run_id ORDER BY seq) AS position_in_run
FROM (SELECT *, sum(starts_run) OVER (ORDER BY seq) AS run_id FROM changes);
SELECT * FROM result ORDER BY seq;
SELECT CASE WHEN ((SELECT list(run_id ORDER BY seq) FROM result) = [1, 1, 2, 3, 3, 4, 5, 5, 6] AND (SELECT list(position_in_run ORDER BY seq) FROM result) = [1, 2, 1, 1, 2, 1, 1, 2, 1]) THEN 'as expected' ELSE error('the nine readings were not six runs numbered 1, 1, 2, 3, 3, 4, 5, 5, 6, restarting the position in each run') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

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

- Apache Hop: [`Add value fields changing sequence`](https://hop.apache.org/manual/latest/pipeline/transforms/addfieldschangesequence.html) (read 2026-09-27)
- Pentaho Data Integration: [`Add value fields changing sequence`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)
- Polars: [`Expr.rle_id`](https://docs.pola.rs/api/python/stable/reference/expressions/modify_select.html) (read 2026-09-27)
- VisiData: [`addcol-incr-key`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/incr.py#L40) (read 2026-09-23)