If you do happen to need each part of the result set ordered individually, you can do something like the following:
SELECT 1,A,B,C FROM TABLEABC UNION SELECT 2,D,E,F FROM TABLEDEF ORDER BY 1,2 Bizarre as it may seem, I've used this technique to good effect many times in the past. Basically, I once worked on a a set of reports where I had to return all results in one query (tool limitation) and I need to generate different "sections" in a single report. To do that, I used the technique above. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com Thursday, January 17, 2002, 2:11:20 PM, you wrote: KL> In the order by section use the relative column numbers. Plus, you can not KL> individually order by a single column from each union. Its a comprehensive KL> sort of the entire column. KL> SELECT A,B,C FROM TABLEABC KL> UNION KL> SELECT D,E,F FROM TABLEDEF KL> ORDER BY 1 KL> -----Original Message----- KL> Sent: Thursday, January 17, 2002 12:31 PM KL> To: Multiple recipients of list ORACLE-L KL> Hi, KL> I try to use union and order by first column of first select statment and KL> also first column of second select statment but get error, Any Idea how to KL> do this?? KL> SELECT A,B,C FROM TABLEABC KL> UNION KL> SELECT D,E,F FROM TABLEDEF KL> ORDER BY A,D KL> Hamid Alavi KL> Office 818 737-0526 KL> Cell 818 402-1987 KL> The information contained in this message and any attachments is intended KL> only for the use of the individual or entity to which it is addressed, and KL> may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from KL> disclosure under applicable law. If you have received this message in error, KL> you are prohibited from copying, distributing, or using the information. KL> Please contact the sender immediately by return e-mail and delete the KL> original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).