CODEWITHSUNDEEP
sqlpostgresqlimdb
Armand Asnani
Armand Asnani

Reputation: 23

How do I select rows with 5 distinct values for each value in other columns?

For reference, this is the schema of the table: casts (pid, mid, role)

What I want to do is find the pid(s) such that they have exactly 5 distinct roles in that mid. That is, since this is a table for actors where pid is the actor id, mid is the movie id and role is the role they play, I want to find all the actor ids that have exactly 5 distinct roles in the respective movie ids of which there can be more than one and that I also want these movie ids.

I'm not exactly sure how to do this without say like 5 self-joins but I'd rather not do that since that would be resource heavy.

Sample table data(casts table)

Sample result from query

Thank you in advance.

Upvotes: 0

Views: 237

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270843

Is this what you want?

select pid, mid
from casts
group by pid, mid
having count(distinct role) = 5;

Upvotes: 0

Related Questions