I have a table with id(key).
I was generated a lot of rows. However some rows where not stored because transactions were not completed.
I have total 44000 of rows: first id is 1 & last id is 44045. How could I find ids of 44 absent rows?
- 1Possible duplicate of MySQL get missing IDs from tableRené Höhle– René Höhle2018-12-27 00:56:21 +00:00Commented Dec 27, 2018 at 0:56
- @Stony indeed. it works! thank you for your support!vppy– vppy2018-12-27 00:59:01 +00:00Commented Dec 27, 2018 at 0:59
Add a comment |
1 Answer
You can try something like below, to get the rows where no other row with an ID less 1 that the ID of the current row exists and where the ID is not the minimum (which cannot have a predecessor).
SELECT * FROM elbat t1 WHERE NOT EXISTS (SELECT * FROM elbat t2 WHERE t2.id = t1.id - 1) AND t1.id <> (SELECT min(t2.id) FROM elabt t2); 