CODEWITHSUNDEEP
sqlsql-serverdatabasesql-order-byunion-all
Gayan
Gayan

Reputation: 1563

SQL Merge two tables - Order BY

I have one data table like below.

Select * from TBL_01

Week     Group
Overall  Nicol  
Overall  Julie
Overall  Kelly

Select * from TBL_02

Week     Group
Week1    Nicol
Week1    Julie
Week1    Kelly
Week2    Nicol
Week2    Julie
Week2    Kelly
Week3    Nicol
Week3    Julie
Week3    Kelly

I need to get a result table like below

Week     Group
Overall  Nicol  
Overall  Julie
Overall  Kelly
Week3    Nicol
Week3    Julie
Week3    Kelly
Week2    Nicol
Week2    Julie
Week2    Kelly
Week1    Nicol
Week1    Julie
Week1    Kelly

Here the trickey park here is I need to over by week, If I do Union all of mth these tables and do order by desc for resulting table. It will order as overall, week1, week2 and week3.

But I need to order by Overall,week3,week2 and week1

Appreciate your responses.

Thank you

Upvotes: 0

Views: 85

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269913

You can use union all and then order by:

select week, grp
from ((select week, grp from tbl_01) union all
      (select week, grp from tbl_02)
     ) t
order by charindex(week, 'Overall,Week3,Week2,Week1')

I find that charindex() for this purpose is simpler than a massive case statement.

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

Here is option using a case expression to control the ordering.

order by case [Week] 
    when 'Overall' then 1
    when 'Week3' then 2
    when 'Week2' then 3
    when 'Week1' then 4
end

Upvotes: 2

Related Questions