Finding single instances of value SQL query

[pkailas]

I have a zipcode database. I need to list out the Cities in which there is only one record.

The column is called "City" and the table is called "ZipCodes".

For some reason my brain isn't firing on all cylinders today!

_______
I love small animals, especially with a good brown gravy....

 

[pkailas]

Never mind. I got it.

SELECT distinct City, StateCode, count(*) as theCount
FROM ZipCodes where country = 'USA' group by city, statecode order by statecode, city

_______
I love small animals, especially with a good brown gravy....

 

[markros]

This query is not what you requested. For SQL Server 2005 and up

Select * from (select *, count(*) over (partition by City) as CityRecordsCount from myTable) where CityRecordsCount = 1

 

[gmmastros]

You don't need the distinct because your group by clause guarantees that the data will be distinct. If you want ony cities that are not duplicated (based on zip code) then you could use the having clause.

SELECT   City, StateCode, count(*) as theCount 
FROM     ZipCodes 
Where    country = 'USA'  
group by city, statecode 
Having   Count(*) = 1
order by statecode, city

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom