Alessandro Alessandro - 6 months ago 10
SQL Question

"Symmetric" GROUP BY on multiple columns

I have a table like that:

_id sender_id receiver_id text
1 1 2 xxx
2 2 1 yyy
3 1 2 xyz
4 3 1 abc


I need to do a GROUP BY on both the
sender_id
and
receiver_id
columns, but it should be symmetric, so {1,2} and {2,1} should be considered as the same group.

This query could be executed on a random DBMS so it should be as most standard as possible.

Answer

I think you need two CASE expressions here, since you are grouping on two columns:

GROUP BY CASE WHEN sender_id < receiver_id THEN sender_id
              ELSE receiver_id
         END,
         CASE WHEN sender_id < receiver_id THEN receiver_id
              ELSE sender_id
         END,

If you are using MySQL, then consider using the LEAST(), GREATEST() trick:

SELECT LEAST(sender_id, receiver_id), GREATEST(sender_id, receiver_id)
FROM yourTable
GROUP BY LEAST(sender_id, receiver_id), GREATEST(sender_id, receiver_id)

In SQLite you can use MAX() and MIN() as scalar functions:

SELECT MIN(sender_id, receiver_id), MAX(sender_id, receiver_id)
FROM yourTable
GROUP BY MIN(sender_id, receiver_id), MAX(sender_id, receiver_id)