Show pageOld revisionsBacklinksExport to PDFBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. ====== Find gaps or duplicates in number sequences in MySql ====== <code sql Find all gaps> select l.id + 1 as start from sequence as l left outer join sequence as r on l.id + 1 = r.id where r.id is null </code> <code sql Find first gap> select min(l.id + 1) as start from sequence as l left outer join sequence as r on l.id + 1 = r.id where r.id is null </code> <code sql Find missing range> select start, stop from ( select m.id + 1 as start, (select min(id) - 1 from sequence as x where x.id > m.id) as stop from sequence as m left outer join sequence as r on m.id = r.id - 1 where r.id is null ) as x where stop is not null; </code> <code sql Find duplicates> select id, count(*) from sequence group by id having count(*) > 1; </code> database/mysql/duplicates_and_gaps.txt Last modified: 2019/10/31 09:04by 127.0.0.1