How to find gaps in sequential numbering in mysql?

by

Update

ConfexianMJS provided much better answer in terms of performance.

The (not as fast as possible) answer

Here’s version that works on 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

More Related Contents:

  1. MySQL how to fill missing dates in range?
  2. Get a list of dates between two dates
  3. Check for x consecutive days – given timestamps in database
  4. How can we find gaps in sequential numbering in MySQL?
  5. Why do I get an error from this sub query script?
  6. sql query to modify the selected value from database [closed]
  7. How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
  8. Can I concatenate multiple MySQL rows into one field?
  9. Remove duplicate rows in MySQL
  10. MySQL: @variable vs. variable. What’s the difference?
  11. How to do the Recursive SELECT query in MySQL?
  12. MySQL, update multiple tables with one query
  13. Join between tables in two different databases?
  14. How to select a column name with a space in MySQL [duplicate]
  15. SQL Performance UNION vs OR
  16. Ordering by specific field value first
  17. MySQL “Group By” and “Order By”
  18. How to change the CHARACTER SET (and COLLATION) throughout a database?
  19. MySQL Query to select data from last week?
  20. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  21. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  22. Are there disadvantages to using a generic varchar(255) for all text-based fields?
  23. SQL Query to show nearest date?
  24. Getting all parent rows in one SQL query
  25. Mysql Improve Search Performance with wildcards (%%)
  26. How to insert data to MySQL with auto-incremented column(field)?
  27. Replace Into Query Syntax
  28. How do I delete all the duplicate records in a MySQL table without temp tables
  29. Foreign key constraints: When to use ON UPDATE and ON DELETE
  30. Syntax error: WITH is not valid input in this position

Leave a Comment