How can we find gaps in sequential numbering in MySQL?

by

A better answer

ConfexianMJS provided a much better answer in terms of performance.

The (not as fast as possible) answer

Here’s a version that works on a table of any size (not just on 100 rows):

SELECT (t1.id + 1) as gap_starts_at,
       (SELECT MIN(t3.id) -1 FROM arrc_vouchers t3 WHERE t3.id > t1.id) as gap_ends_at
FROM arrc_vouchers t1
WHERE NOT EXISTS (SELECT t2.id FROM arrc_vouchers t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
  • gap_starts_at – first id in current gap
  • gap_ends_at – last id in current gap

Leave a Comment