Monday, July 21, 2008

How To Use The Order By Clause When Using UNION ALL Clause

SELECT TOP 1000000000 au_id, au_lname, au_fname,state from authors where state = 'CA' ORDER BY au_lname
union all
SELECT TOP 1000000000 au_id, au_lname, au_fname,state from authors where state = 'UT' ORDER BY au_lname


Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'union'.


To Over Come Problem use derived tables, think of these as a self-contained table, a bit like a temporary table within the query

SELECT * FROM (SELECT TOP 1000000000 au_id, au_lname, au_fname,state from authors where state = 'CA' ORDER BY au_lname) AS TEMP_COL_1
UNION ALL
SELECT * FROM (SELECT TOP 1000000000 au_id, au_lname, au_fname,state from authors where state = 'UT' ORDER BY au_lname) AS TEMP_COL_2,


when we want to use order by clause within the particular query then we need top else we will get the following error,
"
Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Msg 1033, Level 15, State 1, Line 3
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

1 comment:

Anonymous said...

Great tip. It helped me out a lot. Keep up the good work