Mysql query

[odinski]

I'd like to select an unused ID within a given range in my table. E.g. the first unused id available between 18000-19000.

 

I've been using:  select max(id) +1 from source where id between 18000 and 19000;

which eventually have lead to the point where the maximum is used. And prior to this, previous ID's have been deleted, hence I currently have over 500 unused IDs within that range...

 

Is it possible to write a mysql query to find that first unused id between a given range? If so, hints on how to do it would be very much appreciated. Thanks in advance to those who reply.

[trq]

Why are you not using auto incrementing id's?

[odinski]

Why are you not using auto incrementing id's?

 

Well, there are number of reasons. One is that I have different ranges for different categories. :-)

[kickstart]

Hi

 

Having ranges of autogenerated ids is representing something generally not a good idea.

 

However, if you must then set up a table (called integers here) with a single column (called i here) with 10 rows with the values 0 to 9. Using this you can generate a range of numbers.

 

For example to get every number between 18000 and 18999 inclusive:-

 

SELECT 18000 + (a.i + (b.i * 10) + (c.i * 100)) AS SomeInt FROM `integers` a, `integers` b, `integers` c

 

You can then JOIN that with your table of existing IDs, but using a LEFT OUTER JOIN to bring back a row even when there isn't an existing ID. Use a WHERE clause to discard any where there is a matching ID in existance. And use MIN to get the lowest one.

 

Something like this:-

 

SELECT MIN(SomeInt)
FROM (SELECT 18000 + (a.i + (b.i * 10) + (c.i * 100)) AS SomeInt FROM `integers` a, `integers` b, `integers` c ) Table1
LEFT OUTER JOIN YourDataTable Table2
ON Table1.SomeInt = Table2.Id
WHERE Table2.Id IS NULL

 

All the best

 

Keith