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? Link to comment https://forums.phpfreaks.com/topic/102754-way-to-get-the-lowest-next-available-in-a-table/ 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. Link to comment https://forums.phpfreaks.com/topic/102754-way-to-get-the-lowest-next-available-in-a-table/#findComment-526269 Share on other sites More sharing options...
tibberous Posted April 24, 2008 Author Share Posted April 24, 2008 There just int's. Link to comment https://forums.phpfreaks.com/topic/102754-way-to-get-the-lowest-next-available-in-a-table/#findComment-526274 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 Link to comment https://forums.phpfreaks.com/topic/102754-way-to-get-the-lowest-next-available-in-a-table/#findComment-526582 Share on other sites More sharing options...
DarkWater Posted April 24, 2008 Share Posted April 24, 2008 Nice use of subqueries. Link to comment https://forums.phpfreaks.com/topic/102754-way-to-get-the-lowest-next-available-in-a-table/#findComment-526584 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.