Getting Next and Previous from MySQL When Dates Are the Same

Question

Right now I have a problem because importing rows to my database from an external source happens so quickly.

If more than one row is created with the exact same publish date, it is impossible to sort by date and scroll through posts sequentially.

Let's say five rows are all generated at exactly 11:22:04 AM:

• Row 1 - 11:22:04 AM
• Row 2 - 11:22:04 AM
• Row 3 - 11:22:04 AM
• Row 4 - 11:22:04 AM
• Row 5 - 11:22:04 AM

If a visitor is viewing Row 3 and wants "the next row" I am going to ask the database to give me the next row published later than or equal to 11:22:04 AM, which is going to be Row 1 no matter what I do.

• If I don't say "or equal to" that would just mean that the visitor would never get to see Row 4 or Row 5, which is just as bad as always getting Row 1.
• Adding the row ID to the order by clause doesn't help because -- again -- it's just going to always give me Row 1 (if I'm currently looking at Row 3).
• Adding a greater than or less than to the where clause doesn't help because -- for example -- if I'm looking at Row 3 and I want "the next" row but say the ID needs to be greater than 3, I'm then never going to get Row 1.

I can cheat the system by updating every row in the database published at the exact same time with the row's ID as the seconds, which would turn the above records into:

• Row 1 - 11:22:01 AM
• Row 2 - 11:22:02 AM
• Row 3 - 11:22:03 AM
• Row 4 - 11:22:04 AM
• Row 5 - 11:22:05 AM

That actually works very well. The problem is that new rows are added every time an administrator imports data, and I can't be constantly updating the database to correct this.

My queries to get next and previous look like this:

// next row
select  t.*
from    table t
where   t.postdate  >= '{$current_date}'
and     t.postdate  < now()
and     t.id        <> {$current_id}
order   by t.postdate
limit   1

// previous row
select  t.*
from    table t
where   t.postdate  <= '{$current_date}'
and     t.postdate  < now()
and     t.id        <> {$current_id}
order   by t.postdate desc
limit   1

(Yes, I have googled this extensively and reviewed several similar questions on Stackoverflow!)

Answer 1

Could you try something like this:

// next row
select  t.*
from    table t
where   CONCAT(t.postdate, t.id)  >= '{$current_date}{$current_id}'
and     t.postdate  < now()
and     t.id        <> {$current_id}
order   by t.postdate
limit   1

// previous row
select  t.*
from    table t
where   CONCAT(t.postdate, t.id)  <= '{$current_date}{$current_id}'
and     t.postdate  < now()
and     t.id        <> {$current_id}
order   by t.postdate desc
limit   1

Answer 2

Assumptions

Main assumption is that ID field is auto_increment.

Make use of it

// next row
select  t.*
from    table t
where   (( t.postdate  = '{$current_date}' AND  t.id > {$current_id})
OR t.postdate  > '{$current_date}')
and     t.postdate  < now()
order   by t.postdate, t.id
limit   1

// previous row
select  t.*
from    table t
where   (( t.postdate  = '{$current_date}' AND  t.id < {$current_id})
OR t.postdate  < '{$current_date}')
and     t.postdate  < now()
order   by t.postdate desc, t.id DESC
limit   1

Its as simple as that.

Maybe play with the exact syntax a little to get your desired result (order by asc or desc, > or <) but it will never select same row.

Bit rusty with sql, so confirm the syntax too, but the explicit id check will always make sure that if same postdate to further distincts by id.

Update

Added ACS or DESC to the ORDER BY t.id to help ensure all rows selectable, as noted in comments

Update

sqlfiddle proving this concept: http://sqlfiddle.com/#!9/70e01/18

my first sqlfiddle, so excuse if mistakes, but works fine to me.