Grouping by multiple columns with equal priority in MySQL

Question

I have a table of data, and I want to be able to output all the data that does not repeat the contents of two columns, an illustration:

Example table:

Column1 | Column2  | Column3
   1         XX        pop
   2         YY        yif
   3         ZZ        pop
   4         PP        pop
   5         XX        pop
   6         YY        yif
   7         PP        Hor
   8         MM        tre
   9         PP        pop
  10         XX        pop   
  11         MM        pop

What I want to do here is output all rows where the values for both Columns 2 and 3 are not repeated, so you will see that Column1 == 1 and Column1 == 5 have the same values in both Column2 and Column3, so they only need to output where Column1 == 1 and not =5 (or =10).

I would like to set the query to output all rows, except where Column1 == 5,6,9,10 because these have both Column2 and Column3 repetition.

Intended output of query: (note: all three columns are required)

Column1 | Column2  | Column3
   1         XX        pop
   2         YY        yif
   3         ZZ        pop
   4         PP        pop
   7         PP        Hor
   8         MM        tre 
  11         MM        pop

I have tried looking into Group By but this only appears to group by one column, or at least groups them from left to right so GROUP BY (Column2, Column3) would GROUP BY all values in Column2 and then values in Column3 rather than treat both columns equally.

I found one possible solution which was to concat the columns beforehand, such as

GROUP BY CONCAT(Column2, '_', Column3)

From Is it possible to GROUP BY multiple columns using MySQL? but this has been vaguely criticised (at least, as an answer to that question), but seems the closest code I've seen to do what I want to do.

• So, how should I structure my GROUP BY clause to reach this outcome?

• Is the GROUP BY CONCAT I have found a good way of approaching this?

Answer 1

GROUP BY on multiple columns is possible. Depending on the result you want, you have to apply extra GROUP functions to the rest of your data. In your case it looks like you want the first column1 index, and unique combinations of (column2,column3)

The following query should do the trick:

SELECT MIN(column1) AS column1,column2,column3
FROM table1
GROUP BY column2,column3
ORDER BY MIN(column1) ASC;

Answer 2

If you don't care about Column1 at all, you don't even need GROUP BY. Just use DISTINCT

SELECT DISTINCT Column2, Column3
FROM the_table
ORDER BY however_you_want
;

Otherwise, Norbert's answer is probably more fitting.