Help on SQL query

I have a following table...

id col2
1 A
1 B
2 A
3 A
3 B
4 B
5 A
5 B
5 C

How can I retrieve records where ONLY col2 = "A"?? In this scenario, I will only want to retrieve the record id = 2, because id 2 only has col2 = A. I don't want other records because B and/or C exists.

Thanks!Try this:

select * from tabA ta
group by id
having count(col2) = 1

Thanks.
Pat|||pat, that won't work for two reasons: first, the non-aggregate columns in the SELECT are not all included in the GROUP BY as they should be, and second, you aren't specifically selecting col2 values of 'A'
select id
from thetable A
where col2 = 'A'
and not exists
( select 1
from thetable
where id = A.id
and col2 <> 'A' )|||Oh yes...

SQL Server has the restriction where we need to specify all columns in the select list in a group by clause. I was probably writing the query in a 'Sybase' style :-)...|||Im kinda confused with the query your wanting to achive, from the looks of it it would just be

select * from tablea
where col2 = 'A'

Not sure exactly if you were looking for something else, be a little more clear on it and I can help ya out :).|||Hi

Is it possible that

id col2
1 A
1 B
2 A
2 A * <-- ;-)
3 A
3 B
4 B
5 A
5 B
5 C
5 A * <-- ;-) and this also

will exist? just tell coz i am trying ot find a similar soln|||I think that R937 gave SAC11585 a clear answer|||Thanks for all the help. R937's solution is exactly what I wanted.|||Originally posted by pathakpr
Oh yes...

SQL Server has the restriction where we need to specify all columns in the select list in a group by clause. I was probably writing the query in a 'Sybase' style :-)...

This is not SQL Server restriction, but rather level 2 ANSI-compliant style. I doubt Sybase would allow you to do anything different...IT WILL NOT! Just tested it! Try it for yourself, but don't blame SQL Server ;)|||what pat may have been thinking is "mysql style"

mysql does not enforce that restriction

mysql lets you write crap like --

select salesman, salesdate, sum(salesamount)
from salestable
group by salesman

i cannot tell you how many times i've seen new mysql people fall into this trap and then wonder why their results are weird

i blame mysql for the "feature"

see 12.7.3 GROUP BY with Hidden Fields (http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.html)