If I specify any value for an auto-increment field, how can I get the next or previous value?

Question

I have a MySQL table with an auto-increment field and I am trying to figure out how to get the next or previous value along with the rest of that row using purely MySQL. The field is AI but the numbers aren't necessarily consecutive as rows get deleted all the time.

field_id 8, 15, 17

This is what I started with:

//to get next value in table:
SELECT MIN(member_id) AS val FROM members WHERE member_id > 15 LIMIT 1

//to get previous value in table:
SELECT MAX(member_id) AS val FROM members WHERE member_id < 15 LIMIT 1

But that was only returning the member_id value. This works to get the next value (but doesn't make a difference if I use DESC or ASC (wtf):

SELECT MIN(member_id),members.* 
AS val FROM members 
WHERE member_id > 15 
ORDER BY member_id DESC 
LIMIT 1

That query reversed (to get the the previous value) always returns the lowest value in the table (1):

SELECT MAX(member_id),members.* 
FROM members 
WHERE member_id < 15 
ORDER BY member_id ASC 
LIMIT 1

These two queries, however, shows what I want but I actually am not entirely sure why:

//get next LOWEST row
SELECT MAX(member_id),members.* 
FROM members 
WHERE member_id < 15 
GROUP BY member_id    
ORDER BY member_id DESC 
LIMIT 1

//get next HIGHEST row:
SELECT MIN(member_id),members.* 
FROM members 
WHERE member_id > 15 
GROUP BY member_id    
ORDER BY member_id ASC 
LIMIT 1

I'm assuming GROUP BY allows me to pull more than one row from the query? Is there a better way to do this?

Answer 1

//to get next value in table:
SELECT * FROM members WHERE member_id > 15 ORDER BY member_id LIMIT 1

//to get previous value in table:
SELECT * FROM members WHERE member_id < 15 ORDER BY member_id DESC LIMIT 1

Answer 2

MIN() and MAX() are for getting the minimum or maximum value in a column for a group of same values in another column. For example getting a student's minimum and maximum grade. You would GROUP BY the Id of the student and MySQL would give you a single row with those two values.

As @AndreKR shows in his answer. MIN() and MAX() don't affect the where statement.

I'm not entirely sure what you're trying to do ... Why are you only looking at the first and last 15 rows. If you're trying to get the last value used by auto_increment after you do an INSERT you can run this query:

SELECT LAST_INSERT_ID(); and it will return the ID of the last INSERT statement (In that thread!). Auto increment always counts up so it will not reuse rows that have been deleted. The next ID is always the value of LAST_INSERT_ID() + 1.

Hope that helps.

Answer 3

Well that was easy. Thanks AndreKR, but a small correction:

//next value
SELECT * FROM members WHERE member_id > 15 LIMIT 1

//previous value
SELECT * FROM members WHERE member_id < 15 ORDER BY member_id DESC LIMIT 1