Getting records that only exist once.

phillzee · #1 (**permalink**) 12-14-04, 07:10

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

r937 · #2 (**permalink**) 12-14-04, 07:50

Code:

select total
     , num_dels
     , company 
  from nominput 
 where company in 
     ( select company
         from nominput
       group by company
       having count(*) = 1 )

phillzee · #3 (**permalink**) 12-14-04, 10:57

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

Internet Services

Web Development

Digital Marketing

Consumer Tech