2

I was wondering how to select next and previous rows from a mysql database with reference to my currently selected row.

I found this question on SO How to get next/previous record in MySQL? but in this case the select is done based on higher and lower id from the reference. I would like to use earlier or later timestamp instead of ids.

Community
  • 1
  • 1
jcobhams
  • 796
  • 2
  • 12
  • 29
  • 1
    "I would like to use earlier or later timestamp instead of ids." -- so you didn't get the general idea on how to do that? – zerkms Jul 10 '13 at 21:57
  • Ohh...no I dnt....please could you point me in the right direction? – jcobhams Jul 10 '13 at 21:59
  • 1
    From the question you referenced, have you tried `select * from foo where sometimestamp = (select min(sometimestamp) from foo where sometimestamp > ?)`, where `?` is some input? – Ryan Jul 10 '13 at 21:59

2 Answers2

8

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
ozahorulia
  • 9,798
  • 8
  • 48
  • 72
  • @Vyren Media: if you're looking to retrieve only one record go with this answer. This is great for when every timestamp in your table is unique. If, however, it's possible to have several rows with the same timestamp in your application, my answer to this question will retrieve them all--or you can alternately add field(s) or calculations to the code @Hast provided in the `ORDER BY` clause so that when one record is returned it's done via the appropriate logic and not just insertion order after timestamp. – Anton Jul 10 '13 at 22:16
0

Based on the article you linked to, you can use:

SELECT *
FROM `my_table`
WHERE `the_timestamp` = (
    SELECT MIN(`the_timestamp`)
    FROM `my_table`
    WHERE `the_timestamp` > 1373493634
);

It's formatted for readability. Replace the timestamp I used (1373493634) with the timestamp you want to find the next record after.

Anton
  • 3,998
  • 25
  • 40