getting previous and next record in MySQL when ordering by date and allowing duplicate dates

Question

I've tried looking up other help for this problem but I'm just not getting it. Suppose I have a table that looks like the one below.

+----+--------------+------------+
| id | date_col     | label      |
+----+--------------+------------+
| 1  | 2010-09-07   | Record 1   |
| 2  | 2010-09-03   | Record 2   |
| 3  | 2010-08-23   | Record 3   |
| 4  | 2010-08-23   | Record 4   |
| 5  | 2010-08-23   | Record 5   |
| 6  | 2010-08-12   | Record 6   |
| 7  | 2010-08-06   | Record 7   |
| 8  | 2010-08-06   | Record 8   |
| 9  | 2010-08-02   | Record 9   |
| 10 | 2010-08-01   | Record 10  |
+----+--------------+------------+

When queried, I order these records according to date_col and use id (or really any other arbitrary column) to help ordering with duplicate dates.

mysql_query("SELECT * FROM table ORDER BY date_col DESC, id DESC");

However, when I query only one of these records at a time, I want to have a previous and a next button to navigate to the next or previous record. My problem is that date_col allows duplicate values and so, for example, the query below does not work for me when determining the next record in sequence. (assume that this_date is the date_col value and this_id is the id value for the current record we are looking at)

mysql_query("SELECT id FROM table WHERE date_col > this_date ORDER BY date_col DESC, id DESC LIMIT 1");

Even this wouldn't work for me:

mysql_query("SELECT id FROM table WHERE date_col > this_date AND NOT id=this_id ORDER BY date_col DESC, id DESC LIMIT 1");

So what I'm going for is something like this - If I'm looking at the record with id #4, since its being ordered by date_col DESC id DESC, the previous record should be id #5 and the next record should be id #3, but I'm not getting these results at all.

Can someone explain how to make this work properly? Any help is much appreciated.

Answer 1

If you're going to have workable PREV and NEXT buttons in your view, you're going to need a solid conceptual foundation for record ordering in your data model. As you have noticed, your date_col doesn't do the trick because it permits duplicates.

It seems like your concept of NEXT(id) means "the row with the smallest ID number whose ID number is greater than the current id number and whose date is greater than the current row's date." That's OK, but it has a flaw: PREV(NEXT(id)) isn't necessarily equal to id in all cases. This may drive your users around the bend, and may drive your program logic into some real kludges to get things to work.

Why not simply use the serial number?

To get the "next" row, either do

SELECT id FROM table WHERE id = current_id +1

or, if your ID numbers are not guaranteed to be contiguous, do

SELECT min(id) AS id FROM table WHERE id > current_id

If you must show the next date instead of the next row with your next button, then do

SELECT id, date_col
  FROM table 
 WHERE id = (   
  SELECT min(id) AS id
    FROM table 
   WHERE date_col > this_date
)

But if you do that be sure to create an index for date_col. There are equivalent versions of all these queries to use for your PREV button.

Answer 2

If you can handle the initial data hit (recordset isn't huge) a very simple workout would be to query ALL into an array, order as necessary via array functions, then grab data based on the array key which is also your ordering. Again, the limitation on this example is that if you're going to be dealing with tons of data, it's probably not the best option.