Q:

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

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?

A:

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).

