MySQL Order by column = x, column asc?

Question

I won't paste the whole query. It looks like this.

SELECT *, MONTH(date_created) as month 
from table 
GROUP BY month ORDER BY month='3', month asc

As it is April and I am querying this year I would have expected the order to be as follows 3, 1, 2, 4.

Instead I get out 1, 2, 4, 3.

How can I change the ORDER BY part of the statement to order the results by selected month first then the rest of months in the year showing sequentially?

Maybe this: http://stackoverflow.com/questions/2175439/mysql-custom-sort — Thomas Ingham, Apr 25 '13 at 14:01
Please stop using `GROUP BY` like this. It's not ANSI standard. — Kermit, Apr 26 '13 at 02:07

score 5 · Accepted Answer · answered Apr 25 '13 at 14:01

5

add DESC

ORDER BY month = '3' DESC, month asc

month='3' is a boolean expression which returns 1 or 0, so basically when the result is zero, it will on the last part of the result.

or without using DESC, use <>

ORDER BY month <> '3', month asc

answered Apr 25 '13 at 14:01

John Woo

258,903
69
498
492

score 0 · Answer 2 · answered Apr 25 '13 at 14:03

0

You have to add DESC or ASC in the first order :

SELECT *, MONTH(date_created) as month 
FROM table 
GROUP BY month ORDER BY month='3' DESC, month ASC

answered Apr 25 '13 at 14:03

JoDev

6,633
1
22
37

score -2 · Answer 3 · answered Apr 25 '13 at 14:14

-2

The solution is ORDER BY FIELD

SELECT * FROM table
GROUP BY month
ORDER BY FIELD(month, 3,1,2,4)

answered Apr 25 '13 at 14:14

Jürgen Steinblock

30,746
24
119
189

MySQL Order by column = x, column asc?

3 Answers3

Linked