CODEWITHSUNDEEP
sqlsql-server-2005
user380432
user380432

Reputation: 4779

SQL ORDER BY months starting with this month

I have a query that returns months 1-12. (INT)

Is there a way to order the results starting on this month desc?

Example 

3
4
5
6
7
8
9
10
11
12
1
2

Thanks!

EDIT: By this month desc, I mean this actual month. MONTH(GetDate())

Upvotes: 1

Views: 3552

Answers (6)

Miky
Miky

Reputation: 1

Much better in terms of execution cost:

Select * From
Group By monthcolumn,firstname,lastname 
ORDER BY DATEPART(YEAR,datecolumn) DESC, DATEPART(MONTH, datecolumn) DESC

Or, this one is a little expensive in terms of execution cost:

Select * From
Group By monthcolumn ,firstname,lastname    
ORDER BY CASE WHEN (DATEPART(MONTH, datecolumn))=3 THEN 1
              WHEN (DATEPART(MONTH, datecolumn))=2 THEN 2
              WHEN (DATEPART(MONTH, datecolumn))=1 THEN 3
              WHEN (DATEPART(MONTH, datecolumn))=12 THEN 4
              WHEN (DATEPART(MONTH, datecolumn))=11 THEN 5
              WHEN (DATEPART(MONTH, datecolumn))=10 THEN 6
              ELSE 0 END  
              ASC

Upvotes: 0

Nilesh Yadav
Nilesh Yadav

Reputation: 419

Consider you have month column in your table with 1,2,3,---,11,12 values

SELECT *, case when `month`>=3 then `month`
          else `month`+12 end as `temp_month`
          FROM `tbl_name` order by `temp_month`

You will get output like

3
4
5
6
7
8
9
10
11
12
1
2

Upvotes: 1

rollin340
rollin340

Reputation: 358

If you do not add years, it is impossible. The reasoning is because there is no way to tell which January or any month came first.

The only solution you have to to simply order them by YEAR first, then by MONTH.

So unless you change the field into a DATE format or include a YEAR column, you can't do anything. Unless you want to arrange them by ID; which was entered first.

So in short: No YEAR = no way.

Upvotes: 0

DaveE
DaveE

Reputation: 3627

Similar to Brad's answer, add a year column, then 

...
ORDER BY year, month

Use ASCending or DESCending sort modifiers depending on exactly what you want to do.

Upvotes: 0

Nitin Midha
Nitin Midha

Reputation: 2268

Try simple maths in order by, if month is smaller then current add 12, not sure what will be the performance impact ...

Order By Case When month(dateColumn) < month(GetDate()) Then month(dateColumn) +12
ELSE month(dateColumn) END

Upvotes: 3

Brad Christie
Brad Christie

Reputation: 101614

You can add a year in there for sort purposes. Make them output something like:

2010-03
2010-04
...
2011-01
2011-02

Pseduo code:

WHERE year >= 2010 AND month >= 3

Not sure what your query is or the data backing it though.

Upvotes: 2

Related Questions