ORDER BY date DESC

[steadyguy]

I have the following query in a script:

SELECT id, date_format(date, '%M %e, %Y') AS date, entry FROM spd ORDER BY date DESC

Now, this works perfectly -- except, that it sorts alphanumerically ... so that 'March 9, 2003' comes before 'March 11, 2003', instead of after, as intended (thus ORDER BY date DESC).

Does anyone have any ideas about how I might go about fixing this?

[Sxooter]

The problem is that you have a table field named date that I'm assuming IS a date field, but you're aliasing the output from date_format as date, so it overrides it. Since date_format outputs a string, and that's what you're ordering by, you get alpha sorting. You can just change that to a different name and it should work

SELECT id, date_format(date, '%M %e, %Y') AS purty_date, entry FROM spd ORDER BY date DESC

should do it.

[steadyguy]

That worked ... thanks.

[Angus]

I have a similar problem, but cant work out what to change. I tried the above suggestions but it didnt seem to work for me. Any help would be greatly appreciated.

$sql_query = mysql_query("SELECT DATE_FORMAT(FROM_UNIXTIME(datetime),'%M %Y') AS month,COUNT(*) AS count FROM images GROUP BY month ORDER BY month");