tibberous Posted April 24, 2008 Share Posted April 24, 2008 Lets say I have a table: Scott 1 Bill 2 Todd 4 Mike 5 I want to get the next available number, which in this case would be 3. Is there a way to do this? Quote Link to comment Share on other sites More sharing options...
DarkWater Posted April 24, 2008 Share Posted April 24, 2008 Not if they're auto_incrementing primary keys....Well, you can, just not automatically. MySQL just goes to the next HIGHEST key. Quote Link to comment Share on other sites More sharing options...
tibberous Posted April 24, 2008 Author Share Posted April 24, 2008 There just int's. Quote Link to comment Share on other sites More sharing options...
moselkady Posted April 24, 2008 Share Posted April 24, 2008 Assume that the number field is called num, try the following code: SELECT num+1 as avail_num FROM `test` AS a WHERE NOT EXISTS (SELECT * FROM `test` AS b WHERE b.num=a.num+1) ORDER BY a.num LIMIT 1 I hope this works Quote Link to comment Share on other sites More sharing options...
DarkWater Posted April 24, 2008 Share Posted April 24, 2008 Nice use of subqueries. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.