Re[2]: UNION

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Thu, 17 Jan 2002 21:02:40 -0800
Message-ID: <F001.003F376E.20020117203527@fatcity.com>

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:jonathan_at_gennick.com * 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: jonathan_at_gennick.com

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: ListGuru_at_fatcity.com (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). Received on Thu Jan 17 2002 - 23:02:40 CST