SQL

Finding and grouping contiguous ranges

SQL islands-and-gaps: collapse sparse IDs into min–max ranges.

Given integers with holes, produce compact ranges (1–4, 7–9, 15).

Method

Number rows in order; subtract value from row number; constant “island id” marks contiguous runs.

SELECT MIN(id) AS range_start,
       MAX(id) AS range_end,
       COUNT(*) AS n
FROM (
  SELECT id,
         id - ROW_NUMBER() OVER (ORDER BY id) AS grp
  FROM tickets
) t
GROUP BY grp
ORDER BY range_start;

Without window functions, emulate carefully with session variables on a single connection.

Uses

Allocation audits
Coverage checks
Missing-sequence detection after imports

Related but different problem: gapless numbering (sequences).