CODEWITHSUNDEEP
sql-serversql-server-2008distinct
Redtopia
Redtopia

Reputation: 5247

sql server - how do I select distinct locations from postal code database?

I'm writing a query that needs to find distinct city/state/country locations in a postal code database that match a city name. The columns are:

Country, PostalCode, City, State, StateAbbr, County, CountryCode, Latitude, Longitude

The result set needs to include all these columns.

Using the query string: "Brooklyn" as an example, I am able to at least assign a rank to each row like this:

select b.*,
  (select count(*) from PostalCodes b2 
   where b.City = b2.City AND b.[State] = b2.[State] AND b.Country = b2.Country) as Rank
from PostalCodes b
where b.City LIKE 'Brooklyn%'

The result set has 66 rows, 14 of them are locations with a single entry in the database, so their rank is 1. The remaining 52 rows are for Brooklyn, NY, which has 52 entries in the database, and is assigned a rank of 52.

How do I get distinct rows based on the Rank, and get all the columns in the DB? I don't care which postal code is returned... I just want a single entry per city/state/country.

I'm using SQL Server 2008.

Upvotes: 0

Views: 2763

Answers (4)

DeanGC
DeanGC

Reputation: 680

Here's another shot. Note that TOP 1 works because you stated you didn't care which specific row was returned first, and this will return an arbitrary row in cases where Rank > 1.

SELECT  X.Rank, Y.*
FROM (  SELECT  B.City, B.State, B.Country, COUNT(*) AS [Rank]
        FROM    PostalCodes B
        GROUP BY B.City, B.State, B.Country
        ) X 
JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY City, State, Country ORDER BY City, State, Country) AS RN,
        *
        FROM Postalcodes)
    Y
ON X.City = Y.City
AND X.State = Y.State
AND X.Country = Y.Country
WHERE Y.RN = 1

Here's another attempt.

Note that query performance is going to be heavily dependent on the indexes available. I believe I have worked with this table before, and as provided by the vendor the indexes are not optimal for this sort of query. 

SELECT  RNK.Rank, PC.*
FROM    Postalcodes PC
JOIN (
    SELECT  MAX(PostalCode) as PostalCode,
            City, 
            StateAbbr, 
            CountryCode, 
            Count(*) AS Rank

    FROM    Postalcodes P
    group by city, stateabbr, countrycode
    ) RNK
ON  RNK.PostalCode = PC.PostalCode
AND RNK.City        = PC.City
AND RNK.StateAbbr   = PC.StateAbbr
AND RNK.CountryCode = PC.CountryCode

Upvotes: 1

DeanGC
DeanGC

Reputation: 680

From your query, you are looking for a count of postal codes by City, State, and Country.

I think this will give you what you are looking for:

SELECT  B.City, B.State, B.Country, COUNT(*) AS Rank
FROM    PostalCodes B
GROUP BY B.City, B.State, B.Country

Upvotes: 0

Sloth Armstrong
Sloth Armstrong

Reputation: 1066

Assuming each record with the same city/state/country combination has the same value in every other field, you can use this query (although it is a bit of a hack):

SELECT b.*,
       PostalCode = (SELECT TOP 1 PostalCode FROM PostalCodes b2 WHERE b.City = b2.City AND b.[State] = b2.[State] AND b.Country = b2.Country),
       StateAbbr = (SELECT TOP 1 StateAbbr FROM PostalCodes b2 WHERE b.City = b2.City AND b.[State] = b2.[State] AND b.Country = b2.Country),
       County = (SELECT TOP 1 County FROM PostalCodes b2 WHERE b.City = b2.City AND b.[State] = b2.[State] AND b.Country = b2.Country), 
       CountryCode = (SELECT TOP 1 CountryCode FROM PostalCodes b2 WHERE b.City = b2.City AND b.[State] = b2.[State] AND b.Country = b2.Country), 
       Latitude = (SELECT TOP 1 Latitude FROM PostalCodes b2 WHERE b.City = b2.City AND b.[State] = b2.[State] AND b.Country = b2.Country), 
       Longitude = (SELECT TOP 1 Longitude FROM PostalCodes b2 WHERE b.City = b2.City AND b.[State] = b2.[State] AND b.Country = b2.Country)

FROM(
SELECT DISTINCT CITY, STATE, COUNTRY
FROM PostalCodes
) b

The problem with your request is typically when you group by a set of fields (in your case city/state/country) you don't include any other columns. This is a way around that, although it is pretty slow and could be optimized.

Upvotes: 1

JVC
JVC

Reputation: 570

select COUNT(City),City,State,Country
from
 PostalCodes
 group by City,State,Country
 order by COUNT(City)

Upvotes: 0

Related Questions