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())

回答1:

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

回答2:

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.

回答3:

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

回答4:

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.

回答5:

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.

回答6:

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