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

开发者_StackOverflow

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

---

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

---

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