CODEWITHSUNDEEP
mysqlselectuniquedistinct
Rok
Rok

Reputation: 41

mySQL - select unique values in two columns

I really can't 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 output - something like distinct(apple) and distinct(color) together and order by weight desc:

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

So I need not only group by fruit, but also color (all unique).

Any advice or solution? Thanks!

Upvotes: 4

Views: 2232

Answers (2)

agtb
agtb

Reputation: 477

I'm not clear why banana|green is preferred to banana|yellow (change RIGHT JOIN to INNER JOIN for tastier fruit) so I hope I've understood 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 please be aware how MySQL handles hidden columns in GROUP BY.

Upvotes: 1

Johan
Johan

Reputation: 76537

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

Upvotes: 0

Related Questions