MySQL ORDER BY FIELD for months

I have a table called months – this contains all 12 months of the calendar, the IDs correspond to the month number.

I will be running a query to retrieve 2 or 3 sequential months from this table, e.g

• April & May
• June, July, August
• December & January

However I want to ensure that whenever December are January and retrieved, that it retrieves them in that order, and not January – December. Here is what I have tried:

SELECT * FROM `months`

WHERE start_date BETWEEN <date1> AND <date2>

ORDER BY
    FIELD(id, 12, 1)

This works for December & January, but now when I try to retrieve January & February it does those in the wrong order, i.e “February – January” – I’m guessing because we specified 1 in the ORDER BY as the last value.

Anybody know the correct way to achieve this? As I mentioned this should also work for 3 months, so for example “November, December, January” and “December, January, February” should all be retrieved in that order.

Answer

If you want December first, but the other months in order, then:

order by (id = 12) desc, id

MySQL treats booleans as numbers, with “1” for true and “0” for false. The desc puts the 12s first.

EDIT:

To handle the more general case, you can use window functions. Assuming the numbers are consecutive, then the issue is trickier. This will work for 2 and 3-month spans:

order by (case min(id) over () > 1 then id end),
         (case when id > 6 1 else 2 end),
         id

I’m reluctant to think about a more general solution based only on months. After all, you can just use:

order by start_date

Or, if you have an aggregation query:

order by min(start_date)

to solve the real problem.