[AccessD] Union Query with ORDER BY

They need to be going in opposite directions.  For instance, 1st select statement needs to be DESC 95021, 95020, 95019, etc.  Otherwise, it will grab the lowest one which could be 00001, 00002, etc.  Opposite (same technique) applies when >95022.  

SELECT 
TOP 5
ZipCode, Svc, SvcMiles
FROM
tblSvcToZipMiles
WHERE
ZipCode<'95022' 
ORDER BY ZipCode DESC


I think I'll create two recordsets since it still runs fast...Obviously, it would have been nice to use union....
Thx - Paul



-----Original Message-----
From: Mike and Doris Manning [mailto:mikedorism at ntelos.net]
Sent: Thursday, July 17, 2003 1:27 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Union Query with ORDER BY


The problem is that your ORDER BY clauses are going in two different
directions... Try making both ASC or both DESC

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Millard, Paul ---
Sr. Developer Analyst ---WGO
Sent: Thursday, July 17, 2003 4:18 PM
To: accessd at databaseadvisors.com
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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com