Alessandro Alessandro - 4 months ago 4x
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
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.


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
         CASE WHEN sender_id < receiver_id THEN receiver_id
              ELSE sender_id

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)