# Need some help to keep something from listing more than once RSS

## 4 replies

Last post Jan 03, 2013 08:15 PM by poweraddict

Member

12 Points

51 Posts

### Need some help to keep something from listing more than once

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

All-Star

15557 Points

2698 Posts

### Re: Need some help to keep something from listing more than once

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

-Tab Alleman

Participant

1278 Points

195 Posts

### Re: Need some help to keep something from listing more than once

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.

• Marked as answer by poweraddict on Jan 03, 2013 07:57 PM

Member

12 Points

51 Posts

### Re: Need some help to keep something from listing more than once

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.