How to Get Next/Previous Record in MySQL

How to get next/previous record in MySQL?

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)

How to get previous/next row when ordering by date/time then ID in MySQL?

Method #1 (requires MySQL 8+):

SQL

-- Previous ID
WITH cte_desc AS (SELECT * FROM `table` ORDER BY `date` DESC, id DESC),
     cte_r AS (SELECT * FROM `table` WHERE id = @r_id)
SELECT id AS prev_id
FROM cte_desc
WHERE `date` < (SELECT `date` FROM cte_r)
   OR `date` = (SELECT `date` FROM cte_r) AND id < (SELECT id FROM cte_r)
LIMIT 1;

-- Next ID
WITH cte_asc AS (SELECT * FROM `table` ORDER BY `date`, id),
     cte_r AS (SELECT * FROM `table` WHERE id = @r_id)
SELECT id AS next_id
FROM cte_asc
WHERE `date` > (SELECT `date` FROM cte_r)
   OR `date` = (SELECT `date` FROM cte_r) AND id > (SELECT id FROM cte_r)
LIMIT 1;

where @r_id is set to the ID of the row you want to find the previous/next for = 8 in your example.

Explanation

Two Common Table Expressions are defined: cte_desc sorts the table and cte_r gets the current row. The query part then finds the top row for which either the date value is strictly less than that of the chosen row or for which it is equal but the id is strictly less.

Online Demo

Dbfiddle demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5380e374f24243d578db28b9f89b9c8c

Method #2 (for earlier MySQL versions)

Similar to above - just slightly longer when there is no CTE support:

SQL

-- Previous ID
SELECT id AS prev_id
FROM (SELECT * FROM `table` ORDER BY `date` DESC, id DESC) sub
WHERE `date` < (SELECT `date` FROM `table` WHERE id = @r_id)
   OR `date` = (SELECT `date` FROM `table` WHERE id = @r_id)
      AND id < (SELECT id FROM `table` WHERE id = @r_id)
LIMIT 1;

-- Next ID
SELECT id AS next_id
FROM (SELECT * FROM `table` ORDER BY `date`, id) sub
WHERE `date` > (SELECT `date` FROM `table` WHERE id = @r_id)
   OR `date` = (SELECT `date` FROM `table` WHERE id = @r_id)
      AND id > (SELECT id FROM `table` WHERE id = @r_id)
LIMIT 1;

Online Demo

Rextester demo: https://rextester.com/MTW78358

Method #3 (Slower? See first comments):

-- Previous ID
SELECT id AS prev_id
FROM `table`
WHERE CONCAT(`date`, LPAD(id, 8, '0')) =
(SELECT MAX(CONCAT(`date`, LPAD(id, 8, '0')))
 FROM `table`
 WHERE CONCAT(`date`, LPAD(id, 8, '0')) < (SELECT CONCAT(`date`, LPAD(id, 8, '0')) 
                                           FROM `table`
                                           WHERE id = @r_id));
-- Next ID
SELECT id AS next_id
FROM `table`
WHERE CONCAT(`date`, LPAD(id, 8, '0')) =
(SELECT MIN(CONCAT(`date`, LPAD(id, 8, '0')))
 FROM `table`
 WHERE CONCAT(`date`, LPAD(id, 8, '0')) > (SELECT CONCAT(`date`, LPAD(id, 8, '0'))
                                           FROM `table`
                                           WHERE id = @r_id));

Online Demo

Rextester demo: https://rextester.com/BSQQL24519

Explanation

The ordering is by date/time then by ID so to simplify the searching, these are concatenated into a single string - but there is the usual snag of a string ordering placing e.g. 10 after 1 rather than after 9. To overcome this, the IDs are padded with zeros up to the number of digits of the maximum integer in MySQL (4294967295) - using the LPAD function. Having done this groundwork, the previous row can then be found by looking for the largest one that is less than the one for the current id value using MAX and a subselect.

Get Previous and Next record from database and loop them

If id is sequential you can do this:

SQL DEMO

SELECT o.id, 
       COALESCE(b.id, (SELECT MAX(ID) FROM Table1)) as before_id,
       COALESCE(a.id, (SELECT MIN(ID) FROM Table1)) as after_id
FROM Table1 o
LEFT JOIN Table1 b
  ON o.id = b.id + 1
LEFT JOIN Table1 a  
  ON o.id = a.id - 1
ORDER BY o.id  

OUTPUT

| id | before_id | after_id |
|----|-----------|----------|
|  1 |         8 |        2 |
|  2 |         1 |        3 |
|  3 |         2 |        4 |
|  4 |         3 |        5 |
|  5 |         4 |        6 |
|  6 |         5 |        7 |
|  7 |         6 |        8 |
|  8 |         7 |        1 |

If ids are not sequential you need use row_number() (mysql ver 8+) or session variables to create a sequence.

how to get next/previous records in mysql based on timestamp

There is no reason for using subqueries.

Next:

SELECT * FROM `my_table`
WHERE `the_timestamp` > 123456
ORDER BY `the_timestamp` ASC
LIMIT 1

Prev:

SELECT * FROM `my_table`
WHERE `the_timestamp` < 123456
ORDER BY `the_timestamp` DESC
LIMIT 1

PHP get next/previous record from MySQL

the query for the previous article:

SELECT id  FROM article WHERE id < currentID AND parent = 2 ORDER BY id DESC LIMIT 1 

the query for the next article:

SELECT id  FROM article WHERE id > currentID AND parent = 2 ORDER BY id ASC LIMIT 1 

---

Related Topics