How to get next/previous record in MySQL?

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.

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

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

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;

edit: Since this answer has been getting a few upvotes lately, I really want to stress the comment I made earlier about understanding that a primary key colum is not meant as a column to sort by, because MySQL does not guarantee that higher, auto incremented, values are necessarily added at a later time.

If you don't care about this, and simply need the record with a higher (or lower) id then this will suffice. Just don't use this as a means to determine whether a record is actually added later (or earlier). In stead, consider using a datetime column to sort by, for instance.

All the above solutions require two database calls. The below sql code combine two sql statements into one.

select * from foo 
where ( 
        id = IFNULL((select min(id) from foo where id > 4),0) 
        or  id = IFNULL((select max(id) from foo where id < 4),0)
      )

How to get next/previous record in MySQL?

Related

Recent Posts