Br0dskive Br0dskive - 1 month ago 16
SQL Question

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, only one entry.

For instance:

Values: 1, 2, 3, 4, 4, 5, 5, 6.

I would like to get: 1, 2, 3, 6.

EDIT:

The problem is:
What actors (name and number of movies) have played roles with a unique character name in more than 199 movies?

These are my tables:

Table "public.filmparticipation"
Column | Type | Modifiers
----------+---------+-----------
partid | integer |
personid | integer | not null
filmid | integer | not null
parttype | text | not null


Table "public.filmcharacter"
Column | Type | Modifiers
---------------+---------+-----------
partid | integer |
filmcharacter | text |
billingpos | integer |


Table "public.person"
Column | Type | Modifiers
-----------+--------------+-----------
personid | integer |
lastname | text | not null
firstname | text |
gender | character(1) |


This is what I have tried so far, although I'm not even close to a solution I think:

SELECT p.firstname, COUNT(fp.filmid)
FROM person p INNER JOIN filmparticipation fp
ON p.personid = fp.personid
INNER JOIN filmcharacter fc
ON fc.partid = fp.partid
GROUP BY p.firstname
HAVING COUNT(fc.filmcharacter) = 1;


Thank you.

Answer

One simple method uses group by and having:

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