select ids with particular entries on another column

user_1783739 0
Viewed 44

Apologies if this exists in another question but I'm unable to locate it. Say I have a table

ID   Type
1     A
1     A
4     A
1     A
1     A
2     A
2     B
3     A
3     A
3     B
4     A
1     A
4     A

I want to select the ids that only have entry 'A' in the Type column for all their row entries. Expected output should be:

ID
1
4

I attempted to do this

select id 
from table 
order by id 
having Type = 'A'

but not getting expected result.

sql-server sql
4 Answers

Aggregation is one approach, but your HAVING clause needs to change:

SELECT ID
FROM yourTable
GROUP BY ID
HAVING MIN(Type) = 'A' AND MIN(Type) = MAX(Type);  -- sargable HAVING clause

Demo

The MIN/MAX equality check in the HAVING clause ensures that any given matching ID has only one value across the entire table. The first MIN assertion ensures that this one value is A.

user_18632290

What you could do is use a conditional aggregate in the HAVING:

SELECT ID
FROM dbo.YourTable
GROUP BY ID
HAVING COUNT(CASE [Type] WHEN 'A' THEN NULL ELSE 1 END) = 0
ORDER BY ID;
user_20299830 
 SELECT T.ID
  FROM YOUR_TABLE AS T
 GROUP BY T.ID
  HAVING MAX(T.TYPE)='A'
user_145355170

You could use this query

SELECT id 
FROM table_name 
GROUP BY id 
HAVING COUNT(DISTINCT type) = 1 AND MAX(type) = 'A'
ORDER BY id;
user_38444580
Related