How to use"Union ALL" to join sql queries each containing order by clause
-
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.
-
Remove the Order By Clause from the first statement and put it only in the last statement
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
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
-
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
-
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 byThat'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
-
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.
-
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.
-
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
-
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 [/url] -
Please note that this thread is more than 3 years old.
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Kingston Dhasian (8/23/2013)
Please note that this thread is more than 3 years old.It just wanted some brains :hehe:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply