Mysql: Get next and previous in duplicated records

user_14121199 0
Viewed 51

My database has two columns ID and Timestamp.

4  1597228600
8  1597228700
12 1597228700
11 1597228800
14 1597228800
9  1597228900
10 1597228900
1  1597228900
2  1597229000

I need to get next (previous) record of the given id and timestamp ordered by timestamp. If the timestamp has duplicates, the record with higher(lower) id should be returned.

In the example Next and Prev records of the 11(1597228800) are 14 and 12. Next and Prev records of the 14(1597228800) are 1 and 11.

I tried to use CASE condition with subquery, but this solution has issues

SELECT id 
FROM tbl 
WHERE timestamp_value >= '1597228800' 
AND id > (case when ( SELECT min(id) min_id FROM tbl WHERE id > 11 AND timestamp_value = 1597228800) is null then 0 else 11 end) 
ORDER BY timestamp_value
LIMIT 1
mysql performance
1 Answers

I think that this will do:

select t.* 
from tablename t 
cross join (select * from tablename where id = ?) i
where t.id in (
  (
    select id from tablename 
    where (id < i.id and timestamp = i.timestamp) or timestamp < i.timestamp
    order by timestamp desc, id desc limit 1
  ),  
  (
    select id from tablename 
    where (id > i.id and timestamp = i.timestamp) or timestamp > i.timestamp
    order by timestamp, id limit 1
  ) 
)

Replace ? with the id that you want to search for.
The 2 subqueries return the ids of the previous and the next id of ?.
See the demo.

user_104988280
Related