Question
Say I have records with ID 3,4,7,9
I want to be able to go from one to another by navigation via next/previous links.
The problem is, that I don't know how to fetch record with nearest higher ID.
So when I have a record with ID 4, I need to be able to fetch next
existing record, which would be 7.
The query would probably look something like
SELECT * FROM foo WHERE id = 4 OFFSET 1
How can I fetch next/previous record without fetching the whole result set and manually iterating?
I'm using MySQL 5.
Answer
next:
select * from foo where id = (select min(id) from foo where id > 4)
previous:
select * from foo where id = (select max(id) from foo where id < 4)
