1

I have a huge table with a couple hundred thousand records. They all have a unique id for the first column though some are missing in the sequence of numbers.

I am trying to show a list of the numbers that are missing individually.

So for example:

10029 10032 10034 10036

I am trying to get it to show:

10030 10031 10033 10035

I found this query but it seems to be missing numbers when there's a range:

SELECT t1.id+1 AS Missing FROM data AS t1 LEFT JOIN data AS t2 ON t1.id+1 = t2.id WHERE t2.id IS NULL

Result:

10030 10033

As you can see, 10031 and 10035 are missing from that list.

Improve this question
3
  • 1
    This question should help: stackoverflow.com/questions/1389605/… Commented Jul 25, 2013 at 15:00
  • If you can create a temporary table containing all the IDs from min(id) to max(id), you can do a left join between them. Commented Jul 25, 2013 at 15:01
  • Is there a way I can do this within the query without creating another table? I am trying to do this as simple as possible so I can use it within a PHP query. I saw another example that used: SELECT a.order_id+1 AS start, MIN(b.order_id) - 1 AS end Commented Jul 25, 2013 at 15:25

1 Answer 1

Reset to default
0

Perhaps something like this as a starting point. Not tested, but might just work

SELECT @min := (SELECT min(id) FROM yourtable); // get the smallest ID in the table SELECT @last := @min; // cache the min value for the where clause SELECT id, id - @last AS difference, @last := id FROM yourtable WHERE id > @min ORDER BY id ASC HAVING difference > 1

it won't give you the individual missing ids, but it'll tell you where the gaps are, and how big they are.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.