order by and UNION

Hi, I got an error "ORDER BY items must appear in the select list if the
statement contains a UNION operator." when trying to put an order clause at
the end ot the union query.
How to set order to the whole set? Thanks.That will order the entire set, if the column exists. Does it? Can you
post the code you're having trouble with?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"js" <js@.someone@.hotmail.com> wrote in message
news:%23FAx7z1IFHA.1248@.TK2MSFTNGP10.phx.gbl...
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause
at
> the end ot the union query.
> How to set order to the whole set? Thanks.
>|||When using the ORDER BY clause in a query with a UNION, you must adhere
to the ANSI SQL-92 standard, which says you cannot use expressions in
the ORDER BY clause, but only the names or ordinal positions of the
columns in the resultset.
Hope this helps,
Gert-Jan
js wrote:
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause a
t
> the end ot the union query.
> How to set order to the whole set? Thanks.|||select colA,colB
from tableA
union
select stuff as colA,blah as ColB
from tableB
order by 1,2 asc
// 1 = colA and 2=colB
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"js" <js@.someone@.hotmail.com> wrote in message
news:%23FAx7z1IFHA.1248@.TK2MSFTNGP10.phx.gbl...
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause
> at the end ot the union query.
> How to set order to the whole set? Thanks.
>