In MySQL, how to get the next row of data while taking into account time changes in the data while sorting by the sort column?

Question

OK, my question is a bit specific and hard to explain. So I'll try to simplify it. I have a MySQL table that displays "prompts" from a website. The prompts are sorted by the integer in the Order column. So the table looks like this: id (incrementing int), prompt (varchar), order (int). Now when the first prompt is shown to the user, it will be shown until the user confirms, then the next prompt will be shown and so on. I came up with a query to get the next tip based on the user's last confirmed tip: SELECThintFROMh

P粉798343415 · Answer

You can't manage it with this logic.

For this you need to maintain an extra column - seen If the user has already seen hints, you can set it to 1 So your query will be -

SELECT 
   hint 
FROM
  hints 
WHERE 
  `order` > last_seen_item_order
   OR seen = 0 
ORDER BY 
   CASE WHEN `order` > last_seen_item_order THEN `order` END DESC
   CASE WHEN `order` <= last_seen_item_order THEN `id` END ASC
LIMIT 1

NOTE - This is what I recommend. You can have many other ideas.

edit - If you want to maintain prompts on a per-user basis, then you may have two options for maintaining seen.

Maintain seen prompts in json format for users.
Create a separate table named user_hints_see, which contains id (increment), user_id, hint_id, seenColumn.