mysql date order problem

hi there,

i have a query here and group some results by month and year:
SELECT DATE_FORMAT(order.paid , "%c. %y" ) AS Month ... ORDER BY Month

now the problem is that this result is sorted "as string":

10 06
11 06
12 06
8 06
9 06

how can i solve this problem?

 

How about convert the month to an integer first.
... ORDER BY CAST(Month as unsigned)

 

that Just about almost does the trick for me...

My dates are going in as 01/12/07

for example ...

I'd like it ordered by the Latest month ... for example it's November.

I want it to go...

11/12/07
01/12/07 ...

Instead it's going from top to bottom;

I tried using DESC instead of ASC - and it just went back to 12/30/05 ....

any ideas on how to get things in order, by Latest Month, Year, and day??

 

You can split them into separate fields and then sort them,
that way you will have full control over the order:

SELECT 
	MONTH(order.paid) AS mm,  
	YEAR(order.paid) AS yy,  
	DAY(order.paid) AS DD 
FROM 
	order
ORDER BY 
	mm desc,yy desc,dd DESC

Code (markup):

 

SELECT
MONTH(order.paid) AS m,
YEAR(order.paid) AS y
FROM
order
ORDER BY
m desc, y desc