Grouping rows by looking at two columns without considering the order

All we need is an easy explanation of the problem, so here it is.

I have a table looks like this one below:

+----+---------+---------+
| ID | VALUE 1 | VALUE 2 |
+----+---------+---------+
| 01 | A       | B       |
| 01 | B       | A       |
| 02 | C       | D       |
| 02 | D       | C       |
+----+---------+---------+

So what I am trying to do is grouping them together by looking at the value1 and value2 without considering the order of this two values, which mean “A and B” is the same as “B and A”.

I am looking for a result looks like this:

+-------+---------+---------+
| COUNT | VALUE 1 | VALUE 2 |
+-------+---------+---------+
| 02    | A       | B       |
| 02    | C       | D       |
+-------+---------+---------+

Does anyone has any idea about how can I get this?

Thanks a lot!

V

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Something like:

SELECT COUNT(*) AS C, V1, V2
FROM   (SELECT CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END AS V1
             , CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END AS V2
        FROM   input_table
       ) AS tbl
GROUP BY V1, V2

should do the trick, but may not be terribly efficient. Any filtering clauses should be added to the inner select, not the outer.

SELECT COUNT(*) AS C
     , CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END AS V1
     , CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END AS V2
FROM   input_table
GROUP BY CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END, CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END

may also work (and may be more efficient) but the code repetition between the GROUP and SELECT clauses may become a maintenance problem.

Of course if you can ensure that the data is always the “right way around” (and this doesn’t break your model in other ways – we can’t tell if it might as your questions gives no detail on which to base a supposition either way) then

SELECT COUNT(*) AS C, Value1, Value2
FROM   input_table
GROUP BY Value1, Value2

is sufficient. You could enforce the two values being the right way around using INSTEAD OF triggers or in your business logic layer (updating existing data should be easy).

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply