4

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.

Improve this question
5
  • add a WHERE-condition Commented Jun 27, 2022 at 11:49
  • You are not filtering the TAG value anywhere so why would it restrict to A? Commented Jun 27, 2022 at 11:49
  • because I do not want to include names that have tags other than A Commented Jun 27, 2022 at 11:50
  • I would like to select the unique names that only have the tag A So what's wrong with select distinct("name") from data where "tag" = 'A' Commented Jun 27, 2022 at 11:52
  • this gives 3 names: Jane, John, Judith. But John also has tag B, and Judith has B and C. I want to exclude those. Commented Jun 27, 2022 at 11:52

3 Answers 3

Reset to default
1

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.)

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Nice trick with max @Tomáš, thanks!
1

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"
);
Improve this answer

2 Comments

thanks! how would you compare your not exists approach with my not in approach?
Not exists is almost always preferable - also note it's redundant to use distinct within an in(...)
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.