# Group values within a distance

Shape / columns

A variant of [Merge near-duplicate values](https://meridian.online/operations/merge-near-duplicate-values).

Cluster values whose edit or similarity distance falls within a threshold, then merge each cluster to one value.

Page: <https://meridian.online/operations/merge-near-duplicate-values/group-values-within-a-distance>

## Status

Runs as a SQL step.

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

## The DuckDB step

cluster values within an edit distance of 1 and merge each cluster to its most common value

```sql
CREATE TEMP TABLE raw AS SELECT * FROM (VALUES
  (1, 'Jon Smith'), (2, 'Jon Smith'), (3, 'Jon Smith'), (4, 'John Smith'), (5, 'John Smith'), (6, 'Jon Smyth'),
  (7, 'Mary Jones'), (8, 'Mary Jones'), (9, 'Mary Jonas'), (10, 'Mary Jonas'), (11, 'Zed'), (12, NULL)
) t(id, name);
-- Jon Smith is 1 from John Smith and from Jon Smyth, which are 2 apart: the cluster follows the chain, so the pairs are linked
-- to a common root before each name takes the most common spelling of its cluster. Mary Jones and Mary Jonas are equally common
-- (2 each), so the tie goes to the value that sorts first.
CREATE TEMP TABLE result AS
WITH RECURSIVE
  counts AS (SELECT name, count(*) AS n FROM raw WHERE name IS NOT NULL GROUP BY name),
  pairs AS (SELECT a.name AS a, b.name AS b FROM counts a JOIN counts b ON levenshtein(lower(a.name), lower(b.name)) <= 1),
  reach(name, root) AS (SELECT name, name FROM counts UNION SELECT p.b, r.root FROM reach r JOIN pairs p ON p.a = r.name),
  clusters AS (SELECT name, min(root) AS cluster_id FROM reach GROUP BY name),
  merged AS (SELECT cluster_id, arg_min(c.name, (-n, c.name)) AS canonical FROM clusters JOIN counts c USING (name) GROUP BY cluster_id)
SELECT r.id, r.name, m.canonical
FROM raw r LEFT JOIN clusters USING (name) LEFT JOIN merged m USING (cluster_id);
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(canonical ORDER BY id) FROM result) = ['Jon Smith', 'Jon Smith', 'Jon Smith', 'Jon Smith', 'Jon Smith', 'Jon Smith', 'Mary Jonas', 'Mary Jonas', 'Mary Jonas', 'Mary Jonas', 'Zed', NULL] AND (SELECT count(DISTINCT name) FROM result) = 6 AND (SELECT count(DISTINCT canonical) FROM result) = 3) THEN 'as expected' ELSE error('the six spellings were not merged to Jon Smith, Mary Jonas (the tie goes to the first by value) and Zed, following the chain through the 1-away pairs, with the missing name left missing') 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).

- OpenRefine: [`Levenshtein`](https://openrefine.org/docs/manual/cellediting#levenshtein-distance) (read 2026-09-27)
- OpenRefine: [`PPM`](https://openrefine.org/docs/manual/cellediting#ppm) (read 2026-09-27)
- Power Query M: [`Table.AddFuzzyClusterColumn`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#transformation) (read 2026-09-27)
- Tableau Prep: [`Spelling`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27)