sql - How to get next/previous record in MySQL?

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 1

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)