CODEWITHSUNDEEP
mysql
Jaco le Grange
Jaco le Grange

Reputation: 80

MySQL find missing numbers in (unique) NON-SEQUENTIAL integer field

I have a column of non-sequential unique integer numbers between 30 and 999.

I need to present a dropdown list populated with the available numbers.

I could use some of the solutions on this forum but they are all meant for sequential number fields.

I could also probably process this using code in my app, but that is much slower. I believe in letting the database engine do as much of the work as possible.

Is there a way to sort a table field (i.e. create a sequential list) before processing it?

Upvotes: 0

Views: 295

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522007

One good way to do this is to simply maintain a sequence of values from 30 to 999 in your MySQL database. Then, you can left join as follows to generate the list of available numbers:

SELECT t1.id
FROM
(
    SELECT 30 AS id UNION ALL      -- replace this inline table
    SELECT 31 UNION ALL            -- with a formal sequence table
    ...
    SELECT 998 UNION ALL
    SELECT 999
) t1
LEFT JOIN yourTable t2
    ON t2.id = t1.id
WHERE
    t2.id IS NULL;

You may replace the inlined subquery aliased as t1 above with a formal sequence table in your database.

Upvotes: 1

Related Questions