Dennis Dennis - 9 months ago 25
MySQL Question

MYSQL distincs entry of two columns even when switched

my mysql table has the following structure:

ID Sender Recipient Text
-- ------ --------- ----
1 10 11 Text
2 10 11 Text
3 11 10 Text
4 11 10 Text
5 12 13 Text
6 13 12 Text
7 14 15 Text
8 14 15 Text
9 17 16 Text
10 16 17 Text


This table represents a private message conversation between two users. I now would like a list were i show each chat only once. Normally i would go with a group by in this case but then i still have a problem because the sender and recipient column can also be the other way around.

I would like to recieve a result like that:

ID Sender Recipient Text
-- ------ --------- ----
1 10 11 Text
2 12 13 Text
3 14 15 Text
4 16 17 Text


So each of the conversations would be unique.
Does anyone have an idea how to solve this?

Answer Source

You could use a query like this:

select
  min(id) as id,
  least(sender,recipient) as sender,
  greatest(sender,recipient) as recipient
  min(text) as text
from
  tablename
group by
  least(sender,recipient),
  greatest(sender,recipient)

(or you can use another aggregated function for the text, like group_concat, or you have to join tablename twice to get the first or the last msg of the chat, but this depends on what you want to return)