MySQL - select unique values ​​in two columns

Question

I really cannot find a simple or even any solution via sql to get unique data from DB (mySQL).

I will give a sample (simplified):

TABLE t

fruit | color | weight ----------------------- apple | red | 34 kiwi | red | 23 banana | yellow | 100 kiwi | black | 3567 apple | yellow | 23 banana | green | 2345 pear | green | 1900 

And now I want the result to be something like a separate (apple) and separate (color) together and order the weight desc:

 kiwi | black | 3567 banana | green | 2345 apple | red | 34 

• pear | green // is not ok because green is already "in use"
• banana | yellow // out of order because the banana is already "in use"

Therefore, I need not only a fruit group, but also a color (all unique).

Any tips or solutions? Thanks!

Answer 1

I do not understand why banana | green prefer bananas | yellow (change RIGHT JOIN to INNER JOIN for more delicious fruit), so I hope I understand what you're trying to do:

 SELECT t1.fruit, t1.color, t1.weight FROM t AS t1 RIGHT JOIN ( SELECT fruit, color, weight FROM t GROUP BY color ) AS t2 ON t1.fruit = t2.fruit AND t1.color = t2.color GROUP BY fruit ORDER BY weight DESC; 

Also remember how MySQL handles hidden columns in GROUP BY .

Answer 2

 SELECT fruit, color, weight FROM t GROUP BY fruit, color HAVING COUNT(*) = 1 ORDER BY weight DESC