CODEWITHSUNDEEP
sqlsqlitesorting
DkPathak
DkPathak

Reputation: 452

How to sort particular list in sql

Hi i am using this query to make the list of events

SELECT *, CASE
WHEN ((month > 9 OR (month =9 and days >= 22)) AND year <= 2012)then 'list1'
WHEN (year <= 2013) then 'list2' 
WHEN (year > 2013)then 'list3' 
end as sortlist from events order by sortlist, date

Now I want to sort the list3 by same sql, order by year. Is this possible?

Upvotes: 1

Views: 151

Answers (2)

DkPathak
DkPathak

Reputation: 452

I got the solution. In order by, i just edited as 

 order by sortlist, case when sortlist='list3' then year else null end,

Upvotes: 0

roman
roman

Reputation: 117420

if you have SQL SERVER 2005 or higher you can do this

SELECT
    *,
    CALC.sortlist
from events as e
    outer apply
    (
        select
                case
                WHEN ((e.month > 9 OR (e.month =9 and e.days >= 22)) AND e.year <= 2012)then 'list1'
                WHEN (e.year <= 2013) then 'list2' 
                WHEN (e.year > 2013)then 'list3' 
            end as sortlist
    ) as CALC
order by
    CALC.sortlist, CALC.date,
    case when CALC.sortlist = 'list3' then e.year else null end

you can also do this, but it's not exactly ordering by year if your case equal 'list3' (I mean if you'll change expression in case you'll need to change order by as well)

SELECT
    *,
    case
        WHEN ((e.month > 9 OR (e.month =9 and e.days >= 22)) AND e.year <= 2012)then 'list1'
        WHEN (e.year <= 2013) then 'list2' 
        WHEN (e.year > 2013)then 'list3' 
    end as sortlist
from events as e
order by
    sortlist, e.date,
    case when e.year > 2013 then e.year else null end

Upvotes: 1

Related Questions