CODEWITHSUNDEEP
mysqlsqlsql-order-by
Nathan Wiles
Nathan Wiles

Reputation: 926

MySQL most recent n entries, ascending?

I'm trying to get a list of the n most recent entries into a database, but sort them ascending.

Obviously I can use the following to get the first n entries:

SELECT owner_id,message 
FROM messages
WHERE thread_id = ? 
ORDER BY time ASC
LIMIT ?

Or this to get the most 5 recent, descending:

SELECT owner_id,message 
FROM messages
WHERE thread_id = ? 
ORDER BY time DESC
LIMIT ?

But how can I get the most recent 5 in ascending order? I can just reverse the order of the array in PHP, but that seems a little inefficient.

Upvotes: 0

Views: 72

Answers (2)

P.W-S
P.W-S

Reputation: 167

select owner_id,message from (
    SELECT owner_id,message,time 
    FROM messages
    WHERE thread_id = ? 
    ORDER BY `time` DESC
    LIMIT ?
) temp
order by time ASC

Upvotes: 3

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

Select * from
(SELECT owner_id,message,time 
FROM messages
WHERE thread_id = ? 
ORDER BY time DESC
LIMIT ?) test
Order by time ASC

Sample fiddle

Upvotes: 5

Related Questions