Eureka Moment Wiki

One eureka moment at the time

User Tools

Site Tools


database:mysql:duplicates_and_gaps

Find gaps or duplicates in number sequences in MySql

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
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
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;
Find duplicates
SELECT id, COUNT(*) FROM 
    SEQUENCE 
    GROUP BY id HAVING COUNT(*) > 1;
database/mysql/duplicates_and_gaps.txt · Last modified: 2018/07/20 09:19 by tplecko