• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

I have the following table, how to make it in ascending order with All option.

I have a table mstquarters where this kind of data is there with fields quarter and level
quarter                          level
Sep - Dec 2010 - P1         1
Jan - Mar 2011 - P2              2
Apr - Jun 2011 - P3              3
Jul - Sep 2011 - P4              4
Oct - Dec 2011 - P5              5
Jan - Mar 2012 - P6              6
Apr - Jun 2012 - P7              7
Jul - Sep 2012 - P8              8

I want the output should come as
All
Sep - Dec 2010 - P1  
Jan - Mar 2011 - P2
Apr - Jun 2011 - P3
Jul - Sep 2011 - P4
Oct - Dec 2011 - P5
Jan - Mar 2012 - P6
Apr - Jun 2012 - P7
Jul - Sep 2012 - P8
i.e ALL, P1, P2, P3... etc
This query is not working

select quarter from(select 'All Quarters' as quarter from mstquarters union select distinct quarter from mstquarters)x order by 1
0
searchsanjaysharma
Asked:
searchsanjaysharma
1 Solution
 
lwadwellCommented:
How about
with mstquarters as(
SELECT 'Sep - Dec 2010 - P1' as Quarter, 1 as Level UNION ALL
SELECT 'Jan - Mar 2011 - P2' as Quarter, 2 as Level UNION ALL
SELECT 'Apr - Jun 2011 - P3' as Quarter, 3 as Level UNION ALL
SELECT 'Jul - Sep 2011 - P4' as Quarter, 4 as Level UNION ALL
SELECT 'Oct - Dec 2011 - P5' as Quarter, 5 as Level UNION ALL
SELECT 'Jan - Mar 2012 - P6' as Quarter, 6 as Level UNION ALL
SELECT 'Apr - Jun 2012 - P7' as Quarter, 7 as Level UNION ALL
SELECT 'Jul - Sep 2012 - P8' as Quarter, 8 as Level
)
SELECT Quarter
FROM (select 'All' as Quarter, 0 as Level
      union all
      select Quarter, Level
      from mstquarters) v
ORDER BY Level

Open in new window


The cte at the top of the SQL is just for my testdata
0
 
searchsanjaysharmaAuthor Commented:
ok
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now