CODEWITHSUNDEEP
sqlunique
Br0dskive
Br0dskive

Reputation: 17

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.

Upvotes: 0

Views: 286

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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
group by p.personid, p.firstname, p.lastname, fc.filmcharacter
having count(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:

select distinct 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
group by p.personid, p.firstname, p.lastname, fc.filmcharacter
having count(distinct fp.filmid) > 199;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

One simple method uses group by and having:

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

Upvotes: 4

Related Questions