Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    5

    Unanswered: Getting records that only exist once.

    Heres one that I'm too dumb to fathom out - have spent hours on it so any help would be greatly received :-)

    I have a database that lists company names. What I need to do is only select those companies that appear in the table once. - bear in mind a distinct wont work for this.

    So far I have tried this:

    select total,num_dels,company from nominput where (select count(company) from nominput) = 1

    But this doesn't return a thing - is this the right way to do it ?

    Many Thanks
    Phil

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select total
         , num_dels
         , company 
      from nominput 
     where company in 
         ( select company
             from nominput
           group by company
           having count(*) = 1 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    5
    Many thanks - works great :-)

    I guess I need to do some more reading up on group by, I dont understand why it works but it does so thanks very much, greatly appreciated.

    Phil

Posting Permissions

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