SELECT distinct top 10 h.City, g.GeogCol1.STDistance(h.GeogCol1) / 1609.344 AS Dist
FROM zip g
JOIN zip h on g.zip <> h.zip
AND g.zip = '30101'
AND h.zip <> '30101'
WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(50 * 1609.344) order by g.GeogCol1.STDistance(h.GeogCol1) / 1609.344
I need to just get the city name to display once, I guess the best way to go about it would be to take the average of each city result and group by that.
SELECT distinct top 10 h.City, AVG(g.GeogCol1.STDistance(h.GeogCol1) / 1609.344) AS Dist
FROM zip g
JOIN zip h
on g.zip <> h.zip AND
g.zip = '30101' AND
h.zip <> '30101'
WHERE g.GeogCol1.STDistance(h.GeogCol1) <= (50 * 1609.344)
GROUP BY h.City
order by AVG(g.GeogCol1.STDistance(h.GeogCol1) / 1609.344)
poweraddict
Member
12 Points
51 Posts
Need some help to keep something from listing more than once
Jan 03, 2013 07:07 PM|LINK
SELECT distinct top 10 h.City, g.GeogCol1.STDistance(h.GeogCol1) / 1609.344 AS Dist
FROM zip g
JOIN zip h on g.zip <> h.zip
AND g.zip = '30101'
AND h.zip <> '30101'
WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(50 * 1609.344) order by g.GeogCol1.STDistance(h.GeogCol1) / 1609.344
I need to just get the city name to display once, I guess the best way to go about it would be to take the average of each city result and group by that.
Here are my current results:
Kennesaw 4.26533264965555
Emerson 5.78470436871981
Acworth 6.03147015508785
Kennesaw 6.13919747327888
Kennesaw 7.18316093241494
Dallas 8.44172965909612
Marietta 9.29855467095533
Woodstock 9.59781703572218
Cartersville 9.64570198637171
Marietta 10.7603324510661
TabAlleman
All-Star
15557 Points
2698 Posts
Re: Need some help to keep something from listing more than once
Jan 03, 2013 07:23 PM|LINK
SELECT TOP 10 h.City, AVG(g.GeogCol1.STDistance(h.GeogCol1) / 1609.344) AS Dist
FROM zip g
JOIN zip h on g.zip <> h.zip
AND g.zip = '30101'
AND h.zip <> '30101'
GROUP BY h.City
HAVING AVG(g.GeogCol1.STDistance(h.GeogCol1))<=(50 * 1609.344)
order by Dist
imobsuz
Participant
1278 Points
195 Posts
Re: Need some help to keep something from listing more than once
Jan 03, 2013 07:25 PM|LINK
Try:
SELECT distinct top 10 h.City, AVG(g.GeogCol1.STDistance(h.GeogCol1) / 1609.344) AS Dist FROM zip g JOIN zip h on g.zip <> h.zip AND g.zip = '30101' AND h.zip <> '30101' WHERE g.GeogCol1.STDistance(h.GeogCol1) <= (50 * 1609.344) GROUP BY h.City order by AVG(g.GeogCol1.STDistance(h.GeogCol1) / 1609.344)Hope this helps.
poweraddict
Member
12 Points
51 Posts
Re: Need some help to keep something from listing more than once
Jan 03, 2013 08:10 PM|LINK
thanks, it worked great.
the one before the accepted answer wasn't accurate.
it takes 4 seconds to run though, but i dont think i can do much about that.
poweraddict
Member
12 Points
51 Posts
Re: Need some help to keep something from listing more than once
Jan 03, 2013 08:15 PM|LINK
it's the 'order by' thats causing it to run for 3 extra seconds