How to get next/previous record in MySQL?

user_72583 0
Viewed 253433

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.

mysql sql
23 Answers

Next row

SELECT * FROM `foo` LIMIT number++ , 1

Previous row

SELECT * FROM `foo` LIMIT number-- , 1

sample next row

SELECT * FROM `foo` LIMIT 1 , 1
SELECT * FROM `foo` LIMIT 2 , 1
SELECT * FROM `foo` LIMIT 3 , 1

sample previous row

SELECT * FROM `foo` LIMIT -1 , 1
SELECT * FROM `foo` LIMIT -2 , 1
SELECT * FROM `foo` LIMIT -3 , 1

SELECT * FROM `foo` LIMIT 3 , 1
SELECT * FROM `foo` LIMIT 2 , 1
SELECT * FROM `foo` LIMIT 1 , 1
user_30027460

Here we have a way to fetch previous and next records using single MySQL query. Where 5 is the id of current record.

select * from story where catagory=100 and  (
    id =(select max(id) from story where id < 5 and catagory=100 and order by created_at desc) 
    OR 
    id=(select min(id) from story where id > 5 and catagory=100 order by created_at desc) )
user_14123130

Here is my answer. I pick up an idea from 'Decent Dabbler' and add the part that is checking if id is between min(id) and max(id). Here is the part for creating my table.

CREATE TABLE Users (
UserID int NOT NULL auto_increment,
UserName varchar(45),
UserNameID varchar(45),
PRIMARY KEY (UserID)

);

Next step is creating a stored procedure that is responsible for getting the previous id.

    CREATE DEFINER=`root`@`localhost` PROCEDURE `printPreviousIDbySelectedIDUser`(
IN ID int,
IN search_name varchar(45)
)
BEGIN
SELECT CONCAT(ns.UserID) AS 'Previous ID' from Users ns
 where ns.UserName=search_name AND ns.UserID IN (select min(ns.UserID) from Users ns where ns.UserID > ID 
 union select max(ns.UserID) from Users ns where  ns.UserID < ID) LIMIT 1 ;
END

The first method is good if the indexes are sorted, but if they are not. For example, if you have indexes: 1,2,7 and you need to get index number 2 in this way much better to use another approach. 

    CREATE DEFINER=`root`@`localhost` PROCEDURE `getPreviousUserID`(
IN ID int,
IN search_name varchar(45)
)
BEGIN
SELECT CONCAT(ns.UserID) AS 'Previous ID' from Users ns
  WHERE ns.UserName=search_name AND  ns.UserID < ID   ORDER BY ns.UserID DESC LIMIT 1;
END
user_107016600

A way to get next and previous id in the table and if there is no next id i select the first one.Same for the previous id

SELECT id FROM foo
    WHERE ( 
    id = IFNULL((SELECT min(id) FROM medialibrary WHERE id > ?),(SELECT id FROM foo LIMIT 1)) 
    or 
    id = IFNULL((SELECT max(id) FROM medialibrary WHERE id < ?),(SELECT id FROM fooORDER BY id DESC LIMIT 1))
user_131279190

100 % working

SELECT * FROM `table` where table_id < 3 ORDER BY `table_id` DESC limit 1
user_103948740
Related