CODEWITHSUNDEEP
databasesql-server-2008
user1066713
user1066713

Reputation: 15

use order by in part of union statement

i want to sort one side of union statement like below code. but it's not possible to order by just part of union statement and when i use view for the part that has order by, it's make me some error about conflict in collate. so, how can i order by just one side of union statement?

   select Creator
        , CreateDate
        , Subject 
     from tblWkfTicket 
   where tblWkfTicket.ID = @tikectID
union 
   select Creator
        , CreateDate
        , '' AS Subject
     from tblWkfPost 
    where TicketID = @tikectID
 order by tblWkfPost.ID
        ;

Upvotes: 0

Views: 338

Answers (2)

collapsar
collapsar

Reputation: 17238

try

   select *
     from (
                   select 1    seq
                        , null postid
                        , emb.Creator
                        , emb.CreateDate
                        , emb.Subject
                     from (         
                               select Creator
                                    , CreateDate
                                    , Subject 
                                 from tblWkfTicket 
                                where tblWkfTicket.ID = @tikectID
                          ) emb
               union all
                   select 2    seq
                        , tblWkfPost.IDpostid
                        , Creator
                        , CreateDate
                        , '' AS Subject
                    from tblWkfPost 
                   where TicketID = @tikectID
          )
order by seq
       , postid
;

which guarantees your ordering requirements

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

You have to write an ORDER BY the applies to the entire result set. Assuming that you actually want the results from the first query to appear before the results from the second query, and for the results of the second query to be sorted by ID, you need to do something like:

select Creator,CreateDate,Subject
from
  (
   select Creator
       , CreateDate
       , Subject,
       , 1 as resSet
       , 0 as ID 
   from tblWkfTicket 
   where tblWkfTicket.ID = @tikectID
  union all
   select Creator
       , CreateDate
       , '' AS Subject
       , 2
       , ID
   from tblWkfPost 
   where TicketID = @tikectID
  ) t
order by
 resSet,
 ID;

Upvotes: 2

Related Questions