2

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.

Alessandro
  • 3,666
  • 2
  • 28
  • 41

1 Answers1

6

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)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you! Does Sqlite support LEAST and GREATEST? – Alessandro Jun 07 '16 at 08:41
  • 2
    `least` and `greatest` aren't ANSI SQL, and in fact, aren't widely supported either. `case` is a better choice for Alessandro's purposes. – Luaan Jun 07 '16 at 08:42
  • Also, worth bearing in mind, `LEAST()` and `GREATEST()` don't behave intuitively *(read: the same as `MIN()` and `MAX()`)* when dealing with `NULL`s. More importantly, they have different numbers of characters and don't line up nicely, meaning that using `CASE` is prettier ;) – MatBailie Jun 07 '16 at 08:56