Hey Forumers

I’m trying to UNION two+ nested queries, and then sort it with an ORDER BY in the end. But I’m getting errors. Here’s the query:


SELECT DISTINCT Count UID_XID AS 'Interactors' FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11,21)
AND UID_XID IN (
SELECT UID_XID FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11,28)
AND VARIABLE_3 IN (‘p’,’Q’)
)

UNION

SELECT Count UID_XID AS 'Interactors' FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (11,33)
AND UID_XID IN (
SELECT UID_XID FROM [TABLE NAME] WHERE
VARIABLE_1 IN ('ASDQ6KJ8un4Q')
AND VARIABLE_2 IN (21,34)
AND VARIABLE_3 IN (‘q’,’X’)
)


ORDER BY UID_XID DESC
GO


Result:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.


Where do I need to put the ORDER BY? I tried in the beginning select statement, and nada.

Also, is there a way implicitly tell SQL SERVER TO give me the results of a series of UNION queries with subqueries to provide results as I write them, not in ascending nor descending order?

I'm using SQL SERVER 2008 R2

Help?

UID_XID must be in the first(topmost) select of the union,
but you renamed it to 'Interactions', so use 'Interactions' in order by.

Wow... that was a lot simpler than the replies I got in other forums. Plus, it had the added benefit of working =)

BTW, do you know if you can implicitly tell SQL to ORDER results by the way you have them written? For whatever reason BASC and BDESC don't work.

Thanks!

in sql, you are not allowed to make stuff up like BASC and BDESC

could you describe what "ORDER results by the way you have them written" means?

I swear I saw that in the help! A way to supposedly break the ascending or descending.

This is what I mean by as written: let's say you have 4 union queries, which result in the following numbers:

23
48
56
94

If I do ORDERY BY [VARIABLE NAME] DESC the result is:

94
56
48
23

But what if I wrote it out to be so that the numbers should come out like this:

48
94
23
56
?

Is there a way to tell SQL: 'hey, don't order it descending or ascending, just order it by the way I typed it in the query window'? If I just leave it be, it'll sort it ascending by default, which isn't what I want either.

Try UNION ALL instead of UNION.
UNION, MINUS and INTERSECT operators always sort combined resultsets and eliminate duplicates
even without ORDER BY at the end you will always get sorted and unique resultset sorted by all columns.

UNION ALL just combine resultsets without sorting and removing duplicates (it preservers the original rows order of individual queries)
and because of lack of the sort it performs much much faster.



select 1 ....
UNION ALL
select 2 ....
UNION ALL
.....
SELECT N

i don't think you can rely on a union query "preserving" the order of the rows of the individual queries

you can force the order like this --

SELECT 1 AS major_sort_key ...
UNION ALL SELECT 2 ...
UNION ALL SELECT 3 ...
ORDER BY major_sort_key

however, there is then no guarantee that the rows within each major_sort_key will have their sequence preserved, but on the other hand, since the order of results of a (sub)select which doesn't have an ORDER BY clause is undefined, this would be okay

The only way to impose order on a result set is with an ORDER BY clause. Any other order is a coincidence.

-PatP

UNION ALL worked! Thanks guys!