How to filter out all records that have duplicates in SQL?

Question

Trying to get this result from a table with duplicates

red
red
red
blue
green

to

blue
green

Totally omitting all the records that has duplicates and only bringing in the unique records

Answer 1

Use GROUP BY and HAVING clause...

    select color
    from table1
    group by color
    having count(color) = 1

Answer 2

If you need more than just the colours.

select *
from paintmess
where colour in (
   select colour 
   from paintmess 
   group by colour 
   having count(*)=1
   );

id	colour
4	blue
5	green

db<>fiddle here