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

Question

I have table like

SELECT id, name, date FROM `table` ORDER BY `date` DESC, id DESC
...
10  |a|2020-01-08 20:40:00
9   |b|2020-01-08 20:40:00
8   |c|2020-01-08 20:40:00
500 |d|2020-01-06 22:49:00
7   |e|2020-01-06 22:00:00
...

How to get next and previous of a record. (ex: i have info of a record with id = 8 then how to get a next record is 9 and a previous record is 500)

Perhaps some sort of offset. Index the data by an incremental ID, then for the previous record offset -1 of the selected record, then offset +1 for the next record — trainmania100, Feb 12 '20 at 15:46
Please define "next" and "previous". You need a column (or a set of columns) which can unambiguously determine the sort order. — Paul Spiegel, Feb 15 '20 at 11:40

Steve Chambers · Accepted Answer · 2020-02-17T22:59:50.983

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.

Since when is MIN or MAX faster than ORDER BY and LIMIT? ... In these queries two of three WHERE clauses can't use an index. That means two times full table scan to get a single row as result. Also: what is `@r_id` doing in `LPAD(id, @r_id, '0')`? — Paul Spiegel, Feb 17 '20 at 12:30
Both are good points. Have now revamped the answer and relegated the original answer to third place :-( — Steve Chambers, Feb 17 '20 at 22:58

farhankureshi71 · Answer 2 · 2020-02-16T16:53:57.183

You will need to find first of all, current record's position available in the current ordered list, then you will be able to find the previous record as well as next record.

PREVIOUS RECORD

SELECT row_number, id,name,`date`
FROM (
    SELECT @row := @row + 1 AS row_number, id,name,`date`
    FROM `table` AS t
    JOIN (SELECT @row := 0) r
    ORDER BY `date` DESC, id DESC
) main
WHERE row_number = (
    SELECT current_row - 1
    FROM (
        SELECT @curRow := @curRow + 1 AS current_row, t.id,t.name,t.`date`
        FROM `table` AS t
        JOIN (SELECT @curRow := 0) r 
        ORDER BY `date` DESC, id DESC
    ) t1
    WHERE id = 8
);

NEXT RECORD

SELECT row_number, id,name,`date`
FROM (
    SELECT @row := @row + 1 AS row_number, id,name,`date`
    FROM `table` AS t
    JOIN (SELECT @row := 0) r
    ORDER BY `date` DESC, id DESC
) main
WHERE row_number = (
    SELECT current_row + 1
    FROM (
        SELECT @curRow := @curRow + 1 AS current_row, t.id,t.name,t.`date`
        FROM `table` AS t
        JOIN (SELECT @curRow := 0) r 
        ORDER BY `date` DESC, id DESC
    ) t1
    WHERE id = 8
);

score 1 · Answer 3 · answered Feb 19 '20 at 08:27

Try this query:

SELECT MAX(a.id) AS id 
FROM   mytable a 
JOIN  (SELECT id,NAME,DATE FROM mytable WHERE id=8) b
ON     a.id <> b.id AND a.date < b.date 
UNION ALL
SELECT MIN(a.id) AS id 
FROM   mytable a 
JOIN  (SELECT id,NAME,DATE FROM mytable WHERE id=8) b
ON     a.id > b.id AND a.date >= b.date;

Fiddle here : https://www.db-fiddle.com/f/gTv6Hyeq9opHW83r6Cxfck/4

Or you can use a variable to single define the value:

SET @val = 8;
SELECT MAX(a.id) AS id 
FROM   mytable a 
JOIN  (SELECT id,NAME,DATE FROM mytable WHERE id=@val) b
ON     a.id <> b.id AND a.date < b.date 
UNION ALL
SELECT MIN(a.id) AS id 
FROM   mytable a 
JOIN  (SELECT id,NAME,DATE FROM mytable WHERE id=@val) b
ON     a.id > b.id AND a.date >= b.date;

aleksanderd · Answer 4 · 2020-02-12T16:16:51.540

0

if you know date value of record with id=8 you can query next:

SELECT * FROM table 
WHERE id <> 8 AND date >= '2020-01-08 20:40:00'
ORDER BY `date` ASC, id ASC
LIMIT 1

and for previous one(inversed):

SELECT * FROM table 
WHERE id <> 8 AND date <= '2020-01-08 20:40:00'
ORDER BY `date` DESC, id DESC
LIMIT 1

if you wish to get both with single query, you can use UNION: https://dev.mysql.com/doc/refman/8.0/en/union.html

edited Feb 12 '20 at 16:16

answered Feb 12 '20 at 15:57

aleksanderd

11
5

i try your sql but it's not correct (9 and 10), and i think in your query previous is second case(reverse) – DeLe Feb 12 '20 at 16:10
right, I means previous is before(above 8) and next is after(below 8)... fixed in the answer, thnx! it works as you wish for now? – aleksanderd Feb 12 '20 at 16:18

derek.wolfe · Answer 5 · 2020-02-13T14:24:14.343

0

If you have at least MySQL 8.0 you could use something like this:

SET @row_number = 0; 
SET @target = 8;

WITH cte AS (
    SELECT (@row_number:=@row_number + 1) AS num, 
           t.*
    FROM (SELECT * 
          FROM table_name 
          ORDER BY date DESC) t
)
SELECT * 
FROM cte 
WHERE num BETWEEN (SELECT num-1 FROM cte WHERE id = @target) 
              AND (SELECT num+1 FROM cte WHERE id = @target);

The CTE gives you a row number ordered by the date column. The second part of the query pulls everything from teh CTE that has a row number within 1 of the target row you specified (in this case row id 8).

MySQL 8.0+ is required for CTEs. IF you do not have at least MySQL 8.0 you would have to use temp tables for this method.

edited Feb 13 '20 at 14:24

answered Feb 12 '20 at 16:31

derek.wolfe

1,086
6
11

what is temp_rows? can u make a version of temp tables? – DeLe Feb 13 '20 at 00:59
Sorry, I just forgot to rename that when I renamed the CTE. Updated. – derek.wolfe Feb 13 '20 at 14:24
i don't use MySQL 8.0+, can you make a difference version to do that? – DeLe Feb 15 '20 at 15:06
8.0 has both CTEs and ROW_NUMBER; don't bother with @row_number; it is being deprecated. – Rick James Feb 17 '20 at 04:47

score 0 · Answer 6 · answered Feb 15 '20 at 06:41

SELECT id, name, date FROM `table` ORDER BY `date` DESC, id DESC LIMIT 1

SELECT id, name, date FROM `table` ORDER BY `date` ASC, id ASC LIMIT 1

If you need both in a single query:

( SELECT id, name, date FROM `table` ORDER BY `date` DESC, id DESC LIMIT 1 )
UNION ALL
( SELECT id, name, date FROM `table` ORDER BY `date` ASC, id ASC LIMIT 1 )

score 0 · Answer 7 · edited May 11 '20 at 20:05

0

If you are using 8.0, you can try this:

SELECT LEAD(id) OVER (ORDER BY id DESC) AS 'Next',id,LAG(id) OVER (ORDER BY id DESC) AS 'Previous'
FROM table
WHERE id = 8
ORDER BY `date` DESC, id DESC

edited May 11 '20 at 20:05

Nimantha

6,405
6
28
69

answered Feb 20 '20 at 08:54

Siva Koteswara Rao

129
1
6

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

7 Answers7

Method #1 (requires MySQL 8+):

SQL

Explanation

Online Demo

Method #2 (for earlier MySQL versions)

SQL

Online Demo

Method #3 (Slower? See first comments):

Online Demo

Explanation

Linked