Hi
I've got a single table, with a autonumber id (called id) ranging from 1 - 159270 so select max(id) from table1 returns 159270, but I think one of the records has been deleted, is there a simple way to find out which id is missing ?
Thanks
+6
A:
Brute Force:
SELECT * FROM table T1
WHERE NOT EXISTS (SELECT * FROM Table T2 WHERE T2.ID = T1.ID - 1)
This should list rows JUST FOLLOWING the deleted ones.
n8wrl
2009-07-13 11:54:03
Thank you very much, soted my problem, simple when you look at it, always forget how simple SQL
spacemonkeys
2009-07-13 11:58:21
This doesn't work if more than one id is missing in a row.
Mercer Traieste
2009-07-13 12:31:06
He did say that it would find list following the deleted ONES, it found what I was looking and proved that somebody/something had been deleting records, so happy
spacemonkeys
2009-07-13 14:11:09
Yes, he did say so :)
Mercer Traieste
2009-07-13 14:19:24
+3
A:
Not working if more than one id is missing in a row... but fast:
select t1.id + 1 as missing_id
from mytable t1
left join mytable t2 on t1.id + 1= t2.id
where t2.id is null
Mercer Traieste
2009-07-13 11:56:56