Distinct nearby City/Zipcode select issues

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Distinct nearby City/Zipcode select issues

Author

Topic

latitude38n
Starting Member

2 Posts

Posted - 2004-03-12 : 21:09:52

I have the following code, which returns a list of nearby cities based on a city name as input. Most cities have multiple zipcodes per city name, thus it can list multiple rows with the same city name, but with different zipcodes like below:

Zip | Cityname
111 belmont
112 belmont
113 belmont
114 san francisco
115 san francisco

---------------- etc----------------

I do not really care about each group of zipcodes. I only need one pair of zipcode/city name like the following:

ZIP | City name
111 belmont
114 San Francisco

How do I change my select to only return a distinct city name. I do not care which if the city/zipcodes it returns from the similar city.

The select statement is below:

CREATE PROCEDURE ZipSearchByCity
@city varchar(40),
@State varchar(5),
@distance int


 AS


SELECT   distinct o.City AS City, o.zip_code, o.State AS State, 
         (3956 * (2 * ASIN(SQRT(
            POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
              COS(z.RADlatitude) *
              COS(o.RADlatitude) *
              POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
         )))) dist

FROM     zipcodes z,
         zipcodes o,
         zipcodes a




WHERE    z.city = @city  AND
z.State = @State AND
         z.zip_code=a.zip_code AND
         (3956 * (2 * ASIN(SQRT(
            POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
              COS(z.RADlatitude) *
              COS(o.RADlatitude) *
              POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
         )))) < @distance
GO

Thanks,

Christian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-12 : 21:14:22

SELECT MIN(ZIP), Cityname
FROM Table1
GROUP BY Cityname

Just incorporate the above code into your select statement.

Tara

latitude38n
Starting Member

2 Posts

Posted - 2004-03-12 : 21:20:44

When I include that code, I get quite a few (7) group by, aggregate clause errors. I do not think that I should group by all the RaDLongititude, etc in the code. Any ideas?

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-15 : 11:10:26

Any field you return must be part of the grouping or a function within the group (min/max/avg etc). What is unique within each row, thats what you want to group by. Your question said you wanted to return (any)zip plus a city, which Tara's code did. You need to expand that to include the state and possibly the expression that you call 'dist'. If dist is unique for each city/state combination, then you group by it, if its not the same then you need to decide if you want the min/max value of it and included it that way. Below, I took your stored proc and put what would make sense for the group by if dist is unique by city.

CREATE PROCEDURE ZipSearchByCity
@city varchar(40),
@State varchar(5),
@distance int

AS

SELECT o.City AS City, min(o.zip_code), o.State AS State,
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) dist

FROM zipcodes z,
zipcodes o,
zipcodes a

WHERE z.city = @city AND
z.State = @State AND
z.zip_code=a.zip_code AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) < @distance
group by o.City ,o.State ,
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
))))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources