Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 18th, 2004, 10:21 AM
Registered User
 
Join Date: Jul 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Order By with Union

I have two tables, TableA and TableB defined as follows,

TableA

A1 int
A2 int
A3 int


TableB

B1 int
B2 int
B3 int


If I try to run this query, SQL Server says syntex failed at the Order By clouse. Is such Order by not allowed in SQL, Any other way to achieve this?

(Select A1, A2 from TableA)

Union All

(Select B1, B2 from TableB Order by B3)

Any help will be appreciated.

Thanks!

 
Old February 18th, 2004, 10:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

First of all, you can not order by a column that is not included in your SELECT list. Secondly, when performing a UNION query the ORDER BY clause must be a column index not a column name, because a UNION query does not have column headings (although SQL Server pretends that it has by picking the column names used in the first query although this is not ANSI compliant). Assuming you want to order the second column (A2 and B2) your query should look like this:

Code:
   SELECT A1, A2
     FROM TableA

UNION ALL

   SELECT B1, B2
     FROM TableB

 ORDER BY 2
 Conceptually, ORDER BY works by producing the final query table with all the queries joined together (if it is a UNION query), then it orders the query results and does not care about what is in the database.

Regards
Owain Williams
 
Old February 18th, 2004, 11:14 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If you need to order by B3 then you will have to include it in your SELECT list. If you do this you will also have to include a column in the first query with an integer data type. You can not order one query and then UNION it with another query, you can only order the entire query. This query should give you the results you require based on what you asked in your post:
Code:
   SELECT A1, A2, 0
     FROM TableA

UNION ALL

   SELECT B1, B2, B3
     FROM TableB

 ORDER BY 3
 All the rows in TableA will come first as the third column for each row will be 0, then all the rows from TableB will be returned and they will be ordered by B3. This assumes that you do not have any negative values in column B3.

Regards
Owain Williams
 
Old February 18th, 2004, 11:33 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

It's difficult with union queries as you can only order across the whole resultset. If both tables had a column called B3 it would be okay or if you included the third column in the select statement for both queries you could say ORDER BY 3. Alternatively you could include the third column and alias it:
Code:
SELECT A1, A2, A3 
FROM tblA

UNION ALL

SELECT B1, B2, B3
FROM tbl B

ORDER BY 3
or
Code:
SELECT A1, A2, A3 AS [OrderCol] 
FROM tblA

UNION ALL

SELECT B1, B2, B3  AS [OrderCol] 
FROM tbl B

ORDER BY [OrderCol]
--

Joe





Similar Threads
Thread Thread Starter Forum Replies Last Post
UNION and ORDER BY darcome SQL Language 11 October 10th, 2009 07:52 AM
UNION query. rupen Access 3 April 28th, 2006 02:49 AM
UNION of Tables neo_jakey Classic ASP Professional 2 January 31st, 2006 03:08 PM
Problem with UNION - Please Help brettk_1 SQL Server 2000 1 November 9th, 2004 08:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.