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
searchsanjaysharmaAsked:
Who is Participating?
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.