MySQL query multiple column ordering

Question

So, I've written a query which should grab 15 most recent results from the 'messages' table, but order results by date in descending direction. My current query is as follows:

SELECT * FROM messages 
    WHERE chatID = 1 
    ORDER BY ID DESC, timeSent ASC 
    LIMIT 15

As you can see, I am using the 'ID DESC' to get the 15 most recent results, but the 'timeSent ASC' isn't ordering the results in the order I wish.

How can I correct my query to achieve this?

Do you want it by date ascending or descending? Not sure if there's a mistake in your post. (You ask by date descending but use ASC keyword in your query.) — user3849602
– user3849602, Commented May 29, 2015 at 10:39

Sougata Bose · Accepted Answer · 2015-05-29 10:53:44Z

2

First fetch the messages by ordering the ID then sort it according to timeSent. You can try this -

SELECT * FROM 
   (SELECT * FROM messages WHERE chatID = 1 ORDER BY ID DESC LIMIT 15) messages_ordered 
ORDER BY timeSent ASC

edited May 29, 2015 at 10:53

answered May 29, 2015 at 10:37

Sougata Bose

31.8k8 gold badges52 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query multiple column ordering

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related