CODEWITHSUNDEEP
mysql
LucasJay
LucasJay

Reputation: 144

GROUP BY one column in MySQL, but only unique groups from another column

I have a single table.

id   title     value
1    title_a    value_z
1    title_a    value_y
1    title_a    value_x

2    title_a    value_z
2    title_a    value_y
2    title_a    value_x

3    title_a    value_z
3    title_a    value_y
3    title_a    value_w

4    title_a    value_z
4    title_a    value_y

5    title_a    value_z
5    title_a    value_y
5    title_a    value_x
5    title_a    value_w

I want to return:

1    title_a
3    title_a
4    title_a
5    title_a

In other words, I want to GROUP BY id, but eliminate any groups that have exactly the same set of 'values'. (id 1 and 2 have identical values x, y and z.. so group them into 1 result). The title doesn't matter for now.

I can visualize the answer so simply, but cannot for the life of me figure out how to write the query. I have tried all the GROUP BYs, DISTINCTs, and subqueries that I can imagine, but it seems as soon as I group by id, there's no way to check the uniqueness of the 'value' sets.

Please help me, Stack Overflow, you're my only hope...

Upvotes: 3

Views: 1355

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26363

You can use GROUP_CONCAT to flatten out the value for each ID.

SELECT
  id,
  title,
  GROUP_CONCAT(DISTINCT value ORDER BY value) AS AllValues
FROM myTable
GROUP BY id, title

The result will be this:

  id     title     AllValues                        
 ------ --------- --------------------------------- 
     1   title_a   value_x,value_y,value_z          
     2   title_a   value_x,value_y,value_z          
     3   title_a   value_w,value_y,value_z          
     4   title_a   value_y,value_z                  
     5   title_a   value_w,value_x,value_y,value_z

Put this into a subquery and take the MIN of the ID and the MIN of the title, grouping by AllValues, and you'll get the desired result:

SELECT
  MIN(id) AS MinID,
  MIN(title) AS MinTitle
FROM (
  SELECT
    id,
    title,
    GROUP_CONCAT(DISTINCT value ORDER BY value) AS AllValues
  FROM myTable
  GROUP BY id, title
) FlattenTitles
GROUP BY AllValues
ORDER BY MinID

Note that the MIN for the title is a guess; you may have other logic that determines which title is chosen if the values differ.

I've tested this query and I tried to add it as a SQL Fiddle, but the SQLFiddle site was giving me a strange error that I don't get here at home.

Upvotes: 2

Related Questions