This is query to get the deleted record ids from a table
SELECT (t1.id +1) AS gap_starts_at, (SELECT MIN( t3.id ) -1 FROM contacts t3 WHERE t3.id > t1.id) AS gap_ends_at FROM contacts t1 WHERE NOT EXISTS (SELECT t2.id FROM contacts t2 WHERE t2.id = t1.id +1) HAVING gap_ends_at IS NOT NULL ORDER BY t1.id LIMIT 0 , 30
OR
SELECT l.id +1 AS
START
FROM contacts AS l
LEFT OUTER JOIN contacts AS r ON l.id +1 = r.id
WHERE r.id IS NULL order by l.id
SELECT (t1.id +1) AS gap_starts_at, (SELECT MIN( t3.id ) -1 FROM contacts t3 WHERE t3.id > t1.id) AS gap_ends_at FROM contacts t1 WHERE NOT EXISTS (SELECT t2.id FROM contacts t2 WHERE t2.id = t1.id +1) HAVING gap_ends_at IS NOT NULL ORDER BY t1.id LIMIT 0 , 30
OR
SELECT l.id +1 AS
START
FROM contacts AS l
LEFT OUTER JOIN contacts AS r ON l.id +1 = r.id
WHERE r.id IS NULL order by l.id
0 comments:
Post a Comment