How to get next item in mysql order by createdAt

user_14664166 0
Viewed 83

My frontend app can list of my items order by createdAt (for sure by calling API):

SELECT * FROM items ORDER BY createdAt

Output is for example:

id: myIdAAA
id: myIdEEE
id: myIdPPP
id: myIdRRR

Id of each item is UUID (there in example just myId*).

I can open detail of item by url /item/detail/myIdPPP and on this detail I have GO TO NEXT item.

The problem is, that I don`t know how to create SQL query to select next item like this:

SELECT * FROM items AFTER myIdEEE ORDER BY createdAt

Should return myIdPPP.

SELECT * FROM items AFTER myIdPPP ORDER BY createdAt

Should return myIdRRR.

It is possible and how?

sql-order-by mysql sql
1 Answers

If I understand correctly, you can use:

SELECT i.*
FROM items i
WHERE i.createdAt > (SELECT i2.createdAt FROM items i2 WHERE i2.id = ?)
ORDER BY i.createdAt DESC
LIMIT 1;

The ? is a parameter placeholder for the item of interest. This returns the next time timewise in the table -- assuming there are no duplicates.

Note: If you really want a unique value for getting the "next" item, then use an auto-incremented column. This is guaranteed to be unique, even if you insert multiple items at the same time.

user_11440350
Related