How to filter out rows that have duplicates in a database request

Question

I have duplicate data in my table colors:

---

red, red, blue, black, yellow, orange, orange

---

I tried to use DISTINCT

SELECT DISTINCT color FROM colors;

but the result I got is red, blue, black, yellow, orange and I just want it to return blue, black, yellow. How can I do that?

Answer 1

You need to use HAVING.

SELECT color
FROM colors
GROUP BY color
HAVING COUNT(*) = 1

This selects the "color" column from the table "colors", groups the values by equality (i.e. into buckets whose "color" value is the same), then filters based on groups who have a count of 1 (i.e. they are unique).