Adam Adam - 6 months ago 14
SQL Question

MySQL - Group By using different values

I have a table of messages and below shows 3 rows - the second and third being between 2 users.

When I query this table and use Group By I get both lines show up but I only want one.

This is the query:

SELECT a.receiver ORDER BY a.datetime DESC LIMIT 25;


Is there a way to limit one row return when both users have been senders/receivers.

thanks

Answer

First you can group by the larger number , then group by the smaller one.

SELECT
    a.sender,
    a.receiver,
    a.message,
    a. READ,
    a.datetime
FROM
    messages AS a
WHERE
    (
        a.sender = '1000000000'
        OR a.receiver = '1000000000'
    )
GROUP BY IF(a.sender > a.receiver, a.sender, a.receiver), 
         IF(a.sender > a.receiver, a.receiver,a.sender) 
ORDER BY    a.datetime DESC
LIMIT 25;

Note:

The first IF picks up the larger number and the second one picks up the smaller number.