Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    19

    Unanswered: Ordering in Conjunction with Unions

    Why does this not work? When I run this query in Query Analyzer, I get the error "Incorrect syntax near the keyword 'UNION'." This seems simple enough...

    Code:
    SELECT * FROM SalesLead WHERE Age = '50' ORDER BY FirstName
    
    UNION ALL
    
    SELECT * FROM SalesLead WHERE Age = '60' ORDER BY FirstName
    Thanks for any advice

    Aaron

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well this

    Code:
       SELECT * FROM SalesLead WHERE Age = '50'
    UNION ALL 
       SELECT * FROM SalesLead WHERE Age = '60'
     ORDER BY FirstName
    Is the same as this

    Code:
       SELECT * FROM SalesLead WHERE Age IN ('50','60')
     ORDER BY FirstName
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2004
    Posts
    19
    This is actually a simplified fictitious query that I am using to demonstrate this error. The reason I have it set up this way is because I need the ordering to be some what strange. Applying a single order by to the entire unioned set will not work. I need to each individual query in the union to define its own ordering rules and to maintain that order when it is unioned with the other querys. Does this make sense?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select * from (
    select top 100 percent * from SalesLead where age = 50 order by firstname) x1
    union all
    select * from (
    select top 100 percent * from SalesLead where age = 60 order by firstname) x2
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Sep 2004
    Posts
    19
    Perfect, thanks guys!

Posting Permissions

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