Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to use"Union ALL" to join sql queries each containing order by clause


How to use"Union ALL" to join sql queries each containing order by clause

Author
Message
Ismail Ahmed
Ismail Ahmed
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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.
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3003 Visits: 5008
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/
Giri Duddu
Giri Duddu
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
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
BaldingLoopMan
BaldingLoopMan
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 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
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7007 Visits: 13559
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
BaldingLoopMan
BaldingLoopMan
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 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.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7007 Visits: 13559
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
Tim Curtin
Tim Curtin
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 50
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
thava
thava
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 556
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
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3003 Visits: 5008
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search