Post reply

union all and ordering the data

  • September 7, 2011 at 7:29 pm

    #1378334

    notoriousdba (9/7/2011)

    Piet,

    Maybe you haven't been in this game long enough to know who Joe Celko is. Not for nothing, but if he talks, you should listen.

    Ha. I hope you don't really believe that.

  • September 8, 2011 at 7:41 am

    #1378535

    On the original topic (instead of the much-deserved Celko-bashing), you can force separate ordering within Union (All) queries by adding a "Sequencer" column.

    For example:

    select 1 as Seq, MyColumnA, MyColumnB

    from MyFirstTable

    union all

    select 2 as Seq, MyColumnA1, MyColumnB1

    from MySecondTable

    order by Seq, MyColumnA, MyColumnB;

    If you don't want the Seq column in the final dataset output by the query, do the selecting in a CTE, then the Order By in the outer query, and leave Seq out of the Select in the outer query. (Or use a derived table in the From clause if you're using a version of SQL Server that doesn't support CTEs, like SQL 2k.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 2 posts - 16 through 16 (of 16 total)

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