You're exactly right, and that solved it! Thank you.
On Nov 22, 2005, at 7:49 AM, [EMAIL PROTECTED] wrote:
Brian Dunning <[EMAIL PROTECTED]> wrote on 11/22/2005 10:43:13
AM:
> I'm using the following to find the nearest 10 records by proximity
> to the ZIP code $zip. The problem is that it only returns one record
> per ZIP code, even when there are multiple records with the same
ZIP:
>
> (Note this uses a temp table, but I already double-checked that all
> the desired records are making it into the temp table.)
>
> SELECT $tmp.zip, $tmp.state, $tmp.name, $tmp.addr1, $tmp.addr2,
> $tmp.addr3, $tmp.city, $tmp.state, $tmp.zip, $tmp.web, $tmp.email,
> $tmp.phone,
> ROUND((3956 * (2 * ASIN(SQRT(
> POWER(SIN((($tmp.lat-zips.lat)*0.017453293)/2),2) +
> COS($tmp.lat*0.017453293) *
> COS(zips.lat*0.017453293) *
> POWER(SIN((($tmp.lon-zips.lon)*0.017453293)/2),2))))),2) AS
distance
> FROM zips,$tmp
> WHERE
> $tmp.account_id='$account_id' AND zips.zip='$zip'
> GROUP BY distance
> order by distance
> limit 10
>
No, it's only returning one row for each unique distance value. It
just so happens that for each instance, there is only one zipcode.
I don't think you need the GROUP BY clause at all to do the
calcuation you want. Take it out and see what happens.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
