Search code examples
mysqlsqlsql-order-bywhere-clausesql-limit

Reverse search query - search from high to low

I want to fetch messages with given parameters:

select * 
  from chat.messages 
 where senderId = 1 
   and receiverId = 0 
    or senderId = 0 
   and receiverId = 1 
 order 
    by id ASC 
 LIMIT 0,10

Instead of start search from the lowest id, I want to reverse it and start with the highest id in order to get the most recent ones.

Solution

  • Do you just want a descending sort?

    select * 
from chat.messages 
where (senderid = 1 and receiverid = 0) 
   or (senderid = 0 and receiverid = 1) 
order by id desc limit 0,10

    Note that I also fixed a logical flaw issue in your where clause: and has higher precedence than or, so the or condition need to be surrounded with parentheses. Otherwise your where clause is equivalent to:

    where senderid = 1 and (receiverid = 0 or senderid = 0) and receiverid = 1

    ... which obviously is not what you wanted.