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:
Great tip. It helped me out a lot. Keep up the good work
Post a Comment