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;
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;
IF picks up the larger number and the second one picks up the smaller number.