Post reply

top 1 and union

  • October 17, 2005 at 3:19 pm

    #91431

    I'm surprised to find that the following statement doesn't work.  I know it's a bit bizarre but I was curious what everybody thought.  It doesn't like the order by statements in the sub queries.  Msg 104: ORDER BY items must appear in the select list if the statement contains a UNION operator.

    Have a great day!

    select (select top 1 col1 from table1 order by col2) as RetVal

    UNION

    select (select top 1 col3 from table2 order by col4) as RetVal

  • October 18, 2005 at 1:58 am

    #597844

    It's really not that surprising if you stop and think about it for a moment.

    How many sorts can you do on a resultset? More than one? That would be very strange, don't you think?

    UNION gives you a unified result from several results combined together. If you want to sort it, you can have an ORDER BY at the bottom, but not in the middle. This is how it's supposed to work.

    /Kenneth

  • October 18, 2005 at 4:06 am

    #597865

    This however does work

    select Col1, Col2 FROM

     (select top 1 Col1, Col2 from TableA order by Col2) a

    UNION

    select Col3, Col4 FROM

     (select top 1 Col3, Col4 from TableB order by Col4) b

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 18, 2005 at 4:41 am

    #597870

    It does work, but it doesn't do anything

    The UNION statement is still unordered. It doesn't matter if each subselect is sorted when the outer select is not.

    /Kenneth

  • October 18, 2005 at 4:47 am

    #597873

    That would be true, if there wasn't a TOP 1 in each of the subqueries.

    The final order of the union is undefined, but it consists of the first records in TableA and Table B, ordered by the respective fields (col2 and col4)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 18, 2005 at 5:16 am

    #597877

    Hehe, yeah, that's true as well.

    Which reminds me then that it seems like 'it depends', since we don't know the purpose of the excercise.

    /Kenneth

  • October 18, 2005 at 9:21 am

    #597966

    Thanks to all for your replies. 

    This all started when I had a rather large view that had a sub-query to return the top 1 as a column result.  I found the behavior when adding a UNION to look to a separate database.  Something like the below example.  A work around is easy enough, I just wanted to see what everybody else thought.

    I'm still a little surprised that it doesn't work.  I would expect that the order by to select which 1 record was returned by the sub-query and the final union results to be in no particular order.

    select FirstName, LastName, (select top 1 CallTopic from CallLog) as LastCallTopic from db1.SalesPeople

    UNION

    select FirstName, LastName, (select top 1 CallTopic from CallLog) as LastCallTopic from db2.Managers

  • October 18, 2005 at 9:26 am

    #597970

    union will do a DISTINCT select of the 2 result sets hence the order changing, use union all of you don't want that to happen.

  • October 18, 2005 at 10:21 am

    #597995

    What about using a max(col2) in the subquery instead of top 1?  That way you don't need an order by.

  • October 18, 2005 at 10:36 am

    #597999

    Thanks again to all. 

    Yes, Max() works fine.  Something like this:

    (select CallTopic from CallLog where ID = (select max(id) from CallLog where....))

    Also note, you still get the error if you use UNION ALL. 

    Cheers! 

     

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply