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
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