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
