CODEWITHSUNDEEP
sqlpostgresql
user554319
user554319

Reputation:

PostgreSQL: Select unique rows where distinct values are in list

Say that I have the following table:

with data as (
select 'John' "name", 'A' "tag", 10 "count"
union all select 'John', 'B', 20
union all select 'Jane', 'A', 30
union all select 'Judith', 'A', 40
union all select 'Judith', 'B', 50
union all select 'Judith', 'C', 60
union all select 'Jason', 'D', 70
)

I know there are a number of distinct tag values, namely (A, B, C, D).

I would like to select the unique names that only have the tag A

I can get close by doing

-- wrong!
select
  distinct("name")
from data
group by "name"
having count(distinct tag) = 1

however, this will include unique names that only have 1 distinct tag, regardless of what tag is it.

I am using PostgreSQL, although having more generic solutions would be great.

Upvotes: 4

Views: 3404

Answers (3)

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

You're almost there - you already have groups with one tag, now just test if it is the tag you want:

select
  distinct("name")
from data
group by "name"
having count(distinct tag) = 1 and max(tag)='A'

(Note max could be min as well - SQL just doesn't have single() aggregate function but that's different story.)

Upvotes: 1

Stu
Stu

Reputation: 32599

You can use not exists here:

select distinct "name" 
from data d 
where "tag" = 'A'
and not exists (
  select * from data d2 
  where d2."name" = d."name" and d2."tag" != d."tag"
);

Upvotes: 1

user554319
user554319

Reputation:

This is one possible way of solving it:

select
  distinct("name")
from data
where "name" not in (
  -- create list of names we want to exclude
  select distinct name from data where "tag" != 'A'
)

But I don't know if it's the best or most efficient one.

Upvotes: 0

Related Questions