Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to use"Union ALL" to join sql queries each containing order by clause Expand / Collapse
Author
Message
Posted Wednesday, March 3, 2010 8:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 12, 2010 11:05 PM
Points: 14, Visits: 33
I have 2 sql statements and each sql statement is having orderby clause when i am use" UNION ALL " to join them i am getting an error.
Post #876492
Posted Wednesday, March 3, 2010 9:28 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:32 AM
Points: 2,652, Visits: 4,731
Remove the Order By Clause from the first statement and put it only in the last statement


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #876521
Posted Thursday, March 4, 2010 4:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, February 4, 2013 7:02 AM
Points: 465, Visits: 94
If you still want order by in both querries use inner querry, but ideally using order by at the end of the both querries should also work for you
Post #876695
Posted Thursday, March 4, 2010 2:55 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, Visits: 457
also u have to ensure that the aliases of everything your selecting must match between the top select and bottom select. these aliases is what you will put in your order by
Post #877205
Posted Thursday, March 4, 2010 3:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
BaldingLoopMan (3/4/2010)
also u have to ensure that the aliases of everything your selecting must match between the top select and bottom select. these aliases is what you will put in your order by

That's not correct. When using UNION or UNION ALL SQL server will use the alias of the first statement for all. Also, it will use the data type with the highest precedence for all queries.
A few examples:
-- will fail
SELECT '1a'AS a
UNION
SELECT 2 AS b

-- will fail
SELECT 1 AS a
UNION
SELECT 2 AS b
ORDER BY B

-- will run
SELECT 1 AS a
UNION
SELECT 2 AS b





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #877217
Posted Thursday, March 4, 2010 3:30 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, Visits: 457
That's not correct. When using UNION or UNION ALL SQL server will use the alias of the first statement for all. Also, it will use the data type with the highest precedence for all queries.
========================================================================
A few examples:

-- will fail
SELECT 1 AS a
UNION
SELECT 2 AS b
ORDER BY B
--if both aliases were the same this wouldnt fail also what advantage is there to having two different aliases because the "b" alias gets lost in the mix. It becomes "a".

-- will run
SELECT 1 AS a
UNION
SELECT 2 AS b
--no order is being applied here. if a=2 and b=1 it would return 2,1

--to your point i suppose there is no reason at all to even alias any but the first select. interesting.
Post #877230
Posted Thursday, March 4, 2010 4:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
I just needed to show that your statement
u have to ensure that the aliases of everything your selecting must match between the top select and bottom select

is just wrong. Nothing more. But nothing less either.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #877256
Posted Friday, August 23, 2013 6:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 7:25 AM
Points: 21, Visits: 35
You can only use ORDER BY at the end of the query as it must put all the sub queries into a singular data set before it can apply the sort.
UNION removes duplicates from multiple subqueries where UNION ALL does not remove them.

Not sure if this is what you're looking for but,
I put a literal column to apply to the final Order By to ensure the sort order of each sub query:

Select a, b, 2 AS Ordinal
From table1
UNION
Select c,d, 1 AS Ordinal
From table2

Order By Ordinal
Post #1487779
Posted Friday, August 23, 2013 6:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
i don't think so well try this kind of one
WITH cte AS 
(
SELECT TOP 10 c.[object_id], c.column_id
FROM sys.[columns] c
WHERE c.column_id < 3
),cte2 AS
(
SELECT TOP 10 c.[object_id], c.column_id
FROM sys.[columns] c
WHERE c.column_id < 3
)

SELECT ROW_NUMBER() OVER (ORDER BY cte.column_id) AS ID, * FROM cte
UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY cte2.[object_id]) AS ID,* FROM cte2





Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
Post #1487792
Posted Friday, August 23, 2013 6:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:32 AM
Points: 2,652, Visits: 4,731
Please note that this thread is more than 3 years old.


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1487799
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse