SQL ORDER BY Alias column

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?
 
J

John Spencer

If LAT or LNG are ever null this will cause an error to be generated. So I
would check that for starters. Also I would look out for any division by
zero.

You can try sorting by the formula or try referring to your query in a
second query and doing the sorting there.

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
(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))))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top