February 24, 2005 at 2:02 pm
i'm trying to join two select statements with union all and order the results but i get the following error message:
Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION operator.
select.....
union all
select....
order by <column_name>
February 24, 2005 at 2:23 pm
You need to post the full SQL - considering that the error relates to columns in the Select and Order By lists and you omitted the column names from both, it is tough to provide an answer.
February 24, 2005 at 2:28 pm
basically, it means you can't do this
select col1, col2 from x
union all
select col1, col2 from y
order by MissingColumnFromSelect
Keep in mind that the order by is done after the union all has executed so you can't order on unselected columns.
February 25, 2005 at 12:36 am
In the order by, you can only use column names from the first select. In pubs, this would work :
select au_id as ID,au_lname,au_fname,phone from authors
union all
select au_id as URN,au_lname,au_fname,phone from authors
order by ID
February 26, 2005 at 4:06 pm
I haven't tested this but it may do the trick for you. Create a derived table (virtual table) from your union statement that includes the sort column and select from that. For example:
Select vt.col_a, vt.col_b
From (Select col_a, col_b, col_for_sort
From T1
Union All
Select col_a, col_b, col_for_sort
From T2) as vt
Order by vt.col_for_sort
August 19, 2005 at 12:29 pm
test
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply