Select Next and Previous record in MySQL

[PloMar]

Hello,
Let say I have records into a MySQL table with IDs: 3, 5, 8, 9, 13; and I want to be able to go from one to another by navigation via next/previous links. But I don't know how to fetch the rows with nearest higher ID (Note that id values are not contiguous).

I use this SQL query to select the record with id 5:

Code: Select all

SELECT * FROM table_name WHERE id = 5

I need to be able to fetch next existing row, which would be 8, and previous record (ID 3).
How can I fetch next and previous record without fetching whole result set and manually iterating (if it's possible in one query)?

Hi,
Try this SQL code that combines two sql statements into one.

Code: Select all

SELECT * FROM table_name 
WHERE (
  id = IFNULL((SELECT MIN(id) FROM table_name WHERE id > 5),0) 
  OR  id = IFNULL((SELECT MAX(id) FROM table_name WHERE id < 5),0)
)