CODEWITHSUNDEEP
mysqlsql-order-by
user007
user007

Reputation: 3243

MySql - order by monthname

I am trying to order mysql query by month name like:

January --  5
February -- 2
March    -- 5
and so on

Here is my query, but its not ordering:

SELECT leave_balance.balance, MonthName(leave_balance.date_added) AS month 
FROM leave_balance WHERE leave_balance.staff_id_staff = $iid 
GROUP BY month,  leave_balance.leave_type_id_leave_type 
HAVING leave_balance.leave_type_id_leave_type = $leaveBalTypID 
ORDER BY month

Kindly tell me where I am doing wrong

Upvotes: 11

Views: 14188

Answers (4)

Masood
Masood

Reputation: 1594

Just add this at the end of your query statement:

ORDER BY str_to_date(MONTH,'%M')

If column name changes in the future you will not need to worry about it.

Upvotes: 9

Dhinakar
Dhinakar

Reputation: 4151

simply order by leave_balance.date_added field. By default it will sort by month.

Upvotes: 2

Lee Irvine
Lee Irvine

Reputation: 3367

try using the built in month function to get the month's number and order by that. For example:

order by month(leave_balance.date_added)

Upvotes: 7

PSR
PSR

Reputation: 40358

you can specify like 

ORDER BY FIELD(MONTH,'January','February','March',...)


SELECT leave_balance.balance, MonthName(leave_balance.date_added) AS month 
FROM leave_balance WHERE leave_balance.staff_id_staff = $iid 
GROUP BY month,  leave_balance.leave_type_id_leave_type 
HAVING leave_balance.leave_type_id_leave_type = $leaveBalTypID 
ORDER BY FIELD(MONTH,'January','February','March',...,'December');

Upvotes: 21

Related Questions