How to get next/previous record in MySQL?

Question

Say I have records with IDs 3,4,7,9 and I want to be able to go from one to another by navitagion via next/previous links. The problem is, that I don't know how to fetch record with nearest higher ID.

So when I have 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 whole result set and manualy iterating?

I'm using MySQL 5.

Answer 1

Select top 1 * from foo where id > 4 order by id asc

Answer 2

next:

select * from foo where id = (select min(foo) where id > 4)

previous:

select * from foo where id = (select max(foo) where id < 4)

Answer 3

SELECT * FROM foo WHERE id>4 ORDER BY id LIMIT 1

Answer 4

Maybe it is wrong guess but maybe what you need you might get using CURSOR

http://dev.mysql.com/doc/refman/5.0/en/cursors.html

Answer 5

In addition to cemkalyoncu's solution:

next record:

SELECT * FROM foo WHERE id > 4 ORDER BY id LIMIT 1;

previous record:

SELECT * FROM foo WHERE id < 4 ORDER BY id DESC LIMIT 1;

Answer 6

You should handle that with and iterator object in your application.