CODEWITHSUNDEEP
sqlmysql
Owen
Owen

Reputation: 790

Select the next/prev mysql

I've written a MySQL statment to select the next item using the items created timestamp, but I have a problem with it when two or more items have the exact same timestamp. To make things more confusing the item ID and created dates are not always in order, see the dummy data below.

ID | created
~~~~~~~~~~~~
10 | 1289780100
11 | 1289780100
12 | 1289780100 <- current item
13 | 1289780100
14 | 1289780050
15 | 1289780150

I need a SQL statement that can select the prev & next item (two queries are fine) by created and then ID. I've tired a few different queries but either gets stuck in a loop or both 'prev' & 'next' are the same.

Thanks, Owen

Upvotes: 1

Views: 475

Answers (2)

user438316
user438316

Reputation: 249

To get prev_id:

SELECT   
  t1.id, t1.name, t1.prev as prev_id  
FROM   
(SELECT mt.id, mt.name, @prev_id as prev, @prev_id := id   
FROM `main_table` mt, (SELECT @prev_id:=0) ni   
ORDER BY name ASC) as t1;

To get next_id - just change ordering

Here is full example

Upvotes: 2

AndreKR
AndreKR

Reputation: 33697

SELECT *
FROM items
WHERE created >= current_item_created AND ID >= current_item_ID
ORDER BY created ASC, ID ASC LIMIT 1,1

SELECT *
FROM items
WHERE created <= current_item_created AND ID <= current_item_ID
ORDER BY created DESC, ID DESC LIMIT 1,1

Upvotes: 2

Related Questions