CODEWITHSUNDEEP
mysqlsql
David Alexandrovich
David Alexandrovich

Reputation: 21

ORDER BY in SQL is not working

I have this SQL query :

SELECT 
       conversations_messages.conversation_id, 
       MAX(conversations_messages.message_date) AS 'conversation_last_reply', 
       MAX(conversations_messages.message_date) > conversations_users.last_view AS 'conversation_unread'
FROM 
        conversations_messages
        LEFT JOIN conversations ON conversations_messages.message_id=conversations.id
        INNER JOIN conversations_users ON conversations_messages.conversation_id=conversations_users.conversation_id
WHERE 
        conversations_users.user_id = $user_id AND 
        conversations_users.deleted=0
GROUP BY 
        conversations_messages.message_id
ORDER BY 
        'conversation_last_reply' DESC

And the query in running well as I want, but only the last line, of the ORDER BY is not working, its not sorting as I requested.

And the only thing that is not working - is the last line - ORDER BY... I tried to change it to ASC and to DESC again but its not responding... * by the way - the field that I'm trying to sort by - is an integer.

Someone knows what is the problem?

Thanks.

Upvotes: 1

Views: 225

Answers (4)

Todd R
Todd R

Reputation: 18516

Have you tried order by conversations_messages.message_date ?

Upvotes: 0

Vijay
Vijay

Reputation: 418

Try without adding the quotes orderby

ORDER BY conversation_last_reply

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25842

you have quotes around your order by

ORDER BY 
    'conversation_last_reply' DESC
----^-----------------------^------ = bad

you should change it to

ORDER BY 
    conversation_last_reply DESC

or use backticks

ORDER BY 
    `conversation_last_reply` DESC

when you order by a string there is no ordering because the value of the string is always the same for every row :)

Upvotes: 1

Peter Lang
Peter Lang

Reputation: 55524

Try

ORDER BY conversation_last_reply

instead of

ORDER BY 'conversation_last_reply'

Your current version sorts by a constant string, so it does not sort at all.

Upvotes: 4

Related Questions