|
|
Posted by Roy Harvey (SQL Server MVP) on 10/30/07 11:45
On Tue, 30 Oct 2007 03:08:23 -0700, signon77 <signon77@yahoo.com>
wrote:
> ORDER BY
> ExternalId desc, Bookname asc, abs([Difference]) desc
>
>*****************************************************************************************************************
>
>I am trying to order the results of the UNIONs by the absolute value
>in the column Difference. 'Diffference' is a reserved word which is
>why it appears in square brackets. However every time I run this
>stored procedure I get the following error message:
>
>"ORDER BY items must appear in the select list if the statement
>contains a UNION operator"
>
>As 'Difference' is clearly being selected why am I getting this error
>message?
Because what you are trying to ORDER BY is an expression,
abs([Difference]), not a column in the result set. You can get away
with an expression with a single SELECT, but not when using UNION.
To get that order you would have to either add a column with that
expression to each SELECT in the UNION, or place the entire query
inside a derived table and ORDER BY in the outer query:
SELECT *
FROM (<your query here without ORDER BY>)
ORDER BY ExternalId desc, Bookname asc, abs([Difference]) desc
Roy Harvey
Beacon Falls, CT
Navigation:
[Reply to this message]
|