Sql Find Unique Values

Im trying to find a statement for selecting unique values. Not like distinct/unique, cause these just remove duplicates. I want to get a list of all the values that are unique, onl

Solution 1:

One simple method uses group by and having:

select val
from t
groupby val
having count(*) = 1;

Solution 2:

You want to count films per actor and character, so you must group by these two.

select p.personid, p.firstname, p.lastname, fc.filmcharacter, count(distinct fp.filmid)
from person p
join filmparticipation fp on fp.personid = p.personid
join filmcharacter fc on fc.partid = fp.partid
groupby p.personid, p.firstname, p.lastname, fc.filmcharacter
havingcount(distinct fp.filmid) >199;

Even if you are only interested in the actors that played some role in at least 200 movies (i.e. no matter which role or if only one role or more than one), you'd do the same first and only then boil that down to unique rows per actor:

selectdistinct p.personid, p.firstname, p.lastname
from person p
join filmparticipation fp on fp.personid = p.personid
join filmcharacter fc on fc.partid = fp.partid
groupby p.personid, p.firstname, p.lastname, fc.filmcharacter
having count(distinct fp.filmid) > 199;