[AccessD] Union Query with ORDER BY

Paul

Would something like the following work ?
val(zipcode) assumes that You ZipCode field is a string .
I'm quite use that You can't Order By twice.


SELECT
TOP 5
ZipCode, Svc, SvcMiles,(1000000 -val(zipcode)) as SortOrder
FROM
tblSvcToZipMiles
WHERE
ZipCode<'95022'

UNION
SELECT
TOP 5
ZipCode, Svc, SvcMiles , (1000000 +val(zipcode)) as SortOrder
FROM
tblSvcToZipMiles
WHERE
ZipCode>'95022'
ORDER BY SortOrder ASC

Dave

==========================

----- Original Message ----- 
From: "Millard, Paul --- Sr. Developer Analyst ---WGO"
<Paul.Millard at freight.fedex.com>
To: <accessd at databaseadvisors.com>
Sent: Thursday, July 17, 2003 4:17 PM
Subject: [AccessD] Union Query with ORDER BY


>
> oops forgot to change subject title on message....
>
> I'm trying to write a UNION SQL query below to get the 5 closest zip codes
in both directions.  To do this, I need to put an ORDER BY clause to get the
closest ones in each direction.  Running the query separately without UNION
everything works fine, however, using the UNION creates a syntax error which
has to do with ORDER BY.
>
> Any ideas?  I would like to keep it as a UNION but worst scenario would be
to run two recordsets...
>
> Thanks,
> Paul Millard
> San Jose, CA
>
>
> SELECT
> TOP 5
> ZipCode, Svc, SvcMiles
> FROM
> tblSvcToZipMiles
> WHERE
> ZipCode<'95022'
> ORDER BY ZipCode DESC
> UNION
> SELECT
> TOP 5
> ZipCode, Svc, SvcMiles
> FROM
> tblSvcToZipMiles
> WHERE
> ZipCode>'95022'
> ORDER BY ZipCode ASC
>
>
> **********************************************************
> This message contains information that is confidential
> and proprietary to FedEx Freight or its affiliates.
> It is intended only for the recipient named and for
> the express purpose(s) described therein.
> Any other use is prohibited.
> ****************************************************************
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>