Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    4

    Unanswered: ORDER BY and UNION together again!

    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?
    Last edited by saulfeliz; 01-26-12 at 17:57.

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by saulfeliz View Post
    SELECT DISTINCT Count UID_XID AS 'Interactors'
    ............
    ORDER BY UID_XID DESC
    UID_XID must be in the first(topmost) select of the union,
    but you renamed it to 'Interactions', so use 'Interactions' in order by.

  3. #3
    Join Date
    Jan 2012
    Posts
    4

    Thanks!

    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!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by saulfeliz View Post
    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.
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2012
    Posts
    4

    make stuff up?!

    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.

  6. #6
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by saulfeliz View Post
    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!
    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
    Last edited by kordirko; 01-26-12 at 19:23.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The only way to impose order on a result set is with an ORDER BY clause. Any other order is a coincidence.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jan 2012
    Posts
    4

    Thanks!

    UNION ALL worked! Thanks guys!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •