order by...

[Anon]

hi,

ok i have a date column set up in mySQL buts its user defined date not mySQL's date function and i wanna get the newest entrys to teh table so here's my query:

$query = "SELECT id, column1, column2, date FROM mytable GROUP BY id ORDER BY date LIMIT 0,40";

the query works fine BUT it mix's the results up so i thought about ASC or DESC.

ASC = puts the results oldest at the top, newest at the bottom.

DESC = is just crap, only displays 1 days entrys e.g. 30/06/02 buts there's new ones entered on 03/07/02.

is there anyway to get the newest entrys displayed from newest to oldest?

Thanks
Vince

[Anon]

Unfortunately if it's not a specialist date or time field, the sort order will be as per the asci character set order.

This means that in a character field (I guess that's what you are using), it will sort in the following order:

01/01
10/01
11/01
13/01
2/01

If you don;t want to convert the database field over, then you'll probably need to load the data into an PHP array, change the text date to the timestamp of that date and then sort on the timestamp.