开发者

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 m开发者_开发问答ean this actual month. MONTH(GetDate())


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


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.


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


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.


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.


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜