Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Smile Unanswered: 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!

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    12
    Try this:

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


    Thanks.
    Pat

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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'
    PHP Code:
    select id
      from thetable A
     where col2 
    'A'
       
    and not exists
           
    select 1
               from thetable
              where id 
    A.id
                
    and col2 <> 'A' 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    India
    Posts
    12
    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 :-)...

  5. #5
    Join Date
    Feb 2004
    Posts
    14
    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 .

  6. #6
    Join Date
    Jan 2004
    Location
    India
    Posts
    31

    Is this case possible?

    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
    Do not walk behind me, for I may not lead.
    Do not walk ahead of me, for I may not follow.
    Do not walk beside me, either.
    Just leave me alone.

    Yogesh Jangam
    http://yogeshjangam.blogspot.com

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Is this case possible?

    I think that R937 gave SAC11585 a clear answer

  8. #8
    Join Date
    Feb 2004
    Posts
    2
    Thanks for all the help. R937's solution is exactly what I wanted.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •