J
James.Comerford
I have a complex query that uses LAT/LONG to determine if records are
within a specified radius in miles. - The SQL works, but I cannot seem
to get it to sort by the distance (alias iDistance)
Here is one the works - BUT DOES NOT SORT
SELECT company, address1, city, state, phone, zip, (SELECT 6378 *
ATN(SQR(1-(SIN(40.675451/57.3)*SIN(LAT/
57.3)+COS(40.675451/57.3)*COS(LAT/57.3)*COS((LNG/57.3)-
(-74.422196/57.3)))^2) /(SIN(40.675451/57.3)*SIN(LAT/
57.3)+COS(40.675451/57.3)*COS(LAT/57.3)*COS((LNG/57.3)-
(-74.422196/57.3)))) FROM ZipData WHERE [ZipCode]=zip) AS iDistance
FROM Customers WHERE zip in (SELECT [ZipCode] FROM ZipData WHERE 5 >
6378 * ATN(SQR(1-(SIN(40.675451/57.3)*SIN(Lat/
57.3)+COS(40.675451/57.3)*COS(LAT/57.3)*COS((LNG/57.3)-
(-74.422196/57.3)))^2) /(SIN(40.675451/57.3)*SIN(LAT/
57.3)+COS(40.675451/57.3)*COS(Lat/57.3)*COS((LNG/57.3)-
(-74.422196/57.3))))) ORDER BY 7
I first tried "ORDER BY iDistance" but that produces an error - I read
somewhere that the ORDER BY # will sort by that column number - but
that does not seem to work. Any Ideas on what I am missing?
within a specified radius in miles. - The SQL works, but I cannot seem
to get it to sort by the distance (alias iDistance)
Here is one the works - BUT DOES NOT SORT
SELECT company, address1, city, state, phone, zip, (SELECT 6378 *
ATN(SQR(1-(SIN(40.675451/57.3)*SIN(LAT/
57.3)+COS(40.675451/57.3)*COS(LAT/57.3)*COS((LNG/57.3)-
(-74.422196/57.3)))^2) /(SIN(40.675451/57.3)*SIN(LAT/
57.3)+COS(40.675451/57.3)*COS(LAT/57.3)*COS((LNG/57.3)-
(-74.422196/57.3)))) FROM ZipData WHERE [ZipCode]=zip) AS iDistance
FROM Customers WHERE zip in (SELECT [ZipCode] FROM ZipData WHERE 5 >
6378 * ATN(SQR(1-(SIN(40.675451/57.3)*SIN(Lat/
57.3)+COS(40.675451/57.3)*COS(LAT/57.3)*COS((LNG/57.3)-
(-74.422196/57.3)))^2) /(SIN(40.675451/57.3)*SIN(LAT/
57.3)+COS(40.675451/57.3)*COS(Lat/57.3)*COS((LNG/57.3)-
(-74.422196/57.3))))) ORDER BY 7
I first tried "ORDER BY iDistance" but that produces an error - I read
somewhere that the ORDER BY # will sort by that column number - but
that does not seem to work. Any Ideas on what I am missing?