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

Trying to get this result from a table with duplicates

red
red
red
blue
green

blue
green

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

CodePudding user response：

Use GROUP BY and HAVING clause...

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

CodePudding user response：

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

id	colour
4	blue
5	green

db<>fiddle here