MAX_EXTENSION=9999 select a.id + 1 from dude a left join dude b on a.id +1 = b.id where b.id is null and a.id < MAX_EXTENSION;
You'll have to have one record in the table for this to work. I've done a better job of this in Oracle because of nested queries, but I think this could give you a good start in mysql. Dave On Fri, Feb 01, 2002 at 10:03:08AM +1000, Neil Silvester wrote: > I hope this doesn't sound like a stupid question. > > Is there anyway of finding missing values in an INT column type? > Say I have a telephone extension guide. Using the MIN() and MAX() functions > I am able to find the start and end ranges. But what about finding the > extensions that are not allocated. > At the moment I am thinking I will need to use an external script that will > query the individual entries in the array of my returned result (SELECT > extensions FROM table ORDER BY extensions). If the difference between > consecutive rows row[x] and row[y] is +1 (or negative depending on ascending > or descending ordering), then all is good. But if the result is +x, I will > need to echo an array to the screen of row[x] ++1 while row[x] < row[y]. > It will take some work, but shouldn't be too hard to implement. > I guess my question is, is there a function in MySQL that will do this, or > at least help me along with it, or has anyone come across a need for this > before. > > > Neil Silvester > Webmaster > Heat and Control > Ph: +61 7 3877 6429 > [EMAIL PROTECTED] > http://www.heatandcontrol.com > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
