mysql query

[ElectroSoft]

Hello everyone,

I was wondering if we can do the following query...

suppose we have a column cotaining these numbers: 1,2,3,4,6,7,8,10,12,14,15...
I need the query to return 5 (which means the least unavailable number)

thank u

[txbakers]

that's tricky. computers don't really know sequences.

You could setup a routine to read through the table and find the missings, based on an increment of 1.

Or you could setup a control table with values 1,2,3,4,5,6,7,8,etc. and do a comparison between the two to see what doesn't match.

I'd love to see some other ideas for this one.

[ChadSEO]

ElectroSoft,

If you can do it if you setup a table with just a list of integers in it. If you have a table called 'numbers' with one column, 'i', then the following query would work:

select min(i) from numbers where i not in (select id from test);

This would return the lowest missing 'id' from the table 'test', assuming that 'numbers' went that high. Not sure if there is a way to do it without the table, someone else may know.

Chad

[ElectroSoft]

Thanks guys!

Having a table of integers is a good (easy,efficient) idea and bad (not professional)...

In the end if I haven't found a better solution I'll use it!

btw, any body had problems with nested queries with mysql 4.3? it seems that nested queries aren't working with me!

Thanks

[FalseDawn]

[msdn.microsoft.com...]

Is an interesting read on the subject.