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

Posted on 2012-08-18
Last Modified: 2012-08-19
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
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
Question by:searchsanjaysharma
    LVL 25

    Accepted Solution

    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

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    SQl Server Count within PIVOT 5 24
    store procedure with 6 parameters - OR 6 47
    sql query help 7 71
    Trigger for audit 26 40
    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now