中文
devexamples

Detect Gaps in a Sequence

Find missing values in an id sequence by comparing each row with the previous one, a common data-integrity audit query.

SqlData Integrity

Code

snippet
SELECT
    prev_id + 1 AS gap_start,
    id - 1 AS gap_end,
    id - prev_id - 1 AS missing_count
FROM (
    SELECT
        id,
        LAG(id) OVER (ORDER BY id) AS prev_id
    FROM invoices
) numbered
WHERE prev_id IS NOT NULL
  AND id - prev_id > 1
ORDER BY gap_start;

Related Examples