How to use the Union statement with a dynamic order by
I don’t use the Union statement to much in my SQL coding but today I needed to use it to combine results from two separate tables. But I ran into an error when I wanted to add in my usual code for doing a dynamic order by. SQL wouldn’t run my query and output this error message.
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
To fix this problem I put my Union statement into a derived table by doing the following.
SELECT * FROM (
SELECT Column1, Column2 FROM Table_A
UNION ALL
SELECT Column1, Column2 FROM Table_B
) DerivedTableName (Column1, Column2)
Next I added in my dynamic Order By code as such.
SELECT * FROM (
SELECT Column1, Column2 FROM Table_A
UNION ALL
SELECT Column1, Column2 FROM Table_B
) DerivedTableName (Column1, Column2)
ORDER BY
CASE @OrderBy WHEN ‘Column1ASC’ THEN Column1 END ASC,
CASE @OrderBy WHEN ‘Column1DESC’ THEN Column1 END DESC
View Similar Posts
- SQL Server Database stuck in Restoring state
- Link Building with Blog Directories
- Convert VB to C# or C# to VB
July 15th, 2008 at 12:42 pm
Hi Friend,
Thank you so much for your post.
I had the same problem as yours and i found this article and i fixed this,
But i have a question for you see if u can help.
by creating a DerivedTableName on the fly will that effect on the performance of the query because i am returning 100 thousand records at a time..
Appreciated your help
srinivas
July 15th, 2008 at 1:20 pm
You will take a hit for using a derived table but it is much better than the alternative of having to use a temporary table. I am using this approach against 30,000 records and haven’t notice any slow downs.
Good Luck,
Joe
April 29th, 2010 at 9:35 am
Hi, Thanks for this post ,
its good one but i need more detail form this query , am implementing Row_number() function for paging purpose but its not working properly , kindly help me ,
May 26th, 2010 at 5:58 am
thx alot i try this solution and worked well