MySQL select most recent record IDs

Question

I have a Message table that has following two columns:

ID
SENT_DATE

Say I have the following records ordered by SENT_DATE:

ID              SENT_DATE

54              2012-09-21 13:25:20
53              2012-09-21 13:25:17
32              2012-09-21 13:25:15
52              2012-09-21 13:25:14
51              2012-09-21 13:25:12
46              2012-09-21 13:25:12
50              2012-09-21 13:25:11

How do I get all new message IDs sent after ID=46 (exclusive)? The query should return the following list:

ID

54
53
32
52
51

Notice that the message IDs don't increase monotonically overtime, as they are generated by different message queues. But the IDs are unique. Also, two messages can be sent at the same time.

Answer 1

You can use a subquery to get the sent_date of the ID you want and return all rows with a greater value:

SELECT `id`
FROM `messages` 
WHERE `sent_date` >= (SELECT `sent_date` FROM `messages` WHERE `id` = 46)
AND `id` <> 46

Answer 2

Using a sub-query will solve this issue.

Handled inclusively:

SELECT `ID` FROM `messages` WHERE `SENT_DATE` >=
    (SELECT `SENT_DATE` FROM `messages` WHERE `ID` = 46)

Handled exclusively:

SELECT `ID` FROM `messages` WHERE `SENT_DATE` >
    (SELECT `SENT_DATE` FROM `messages` WHERE `ID` = 46)

Answer 3

You can do using a subselect for the sent_date of id 46, like so:

select * from message_table where sent_date > (select sent_date from message_table where id = 46)