[Resolved] sort order

[qrt123]

Hi there

I have a MySql database where I have six columns: id, date, month, year, subject, text

Now I wan't to make a query sorted by date, month, year

example:
$result = mysql_query("SELECT id, date, month, year, subject, text FROM news ORDER BY date, month, year DESC LIMIT 5");
while($data = mysql_fetch_array($result)){
extract ($data);

Problem is that I had som news which was shown in the following order:

01-05-2003
30-04-2003
28-04-2003

and that was OK, but when adding news dated 02-05-2003
it ended up looking like:

01-05-2003
02-05-2003
30-04-2003
28-04-2003

How come???

Sincerely
qrt123

[jayant]

ORDER BY date, month, year DESC
means
date is asc order. month in asc order. year in desc order

Note: you should always sort on bigger values first then on smaller ones. i.e first do usig year, then month then date. that will ensure correct order.

ORDER BY year DESC, month DESC, date DESC

[BuzzLY]

If I'm not mistaken, your query sorts by date and month ascending, and year descending. Use this instead:

SELECT id, date, month, year, subject, text FROM news ORDER BY year DESC, month DESC, date DESC LIMIT 5

[edit]Doh! Jinx![/edit]

[qrt123]

Hi There

Thanks a lot.

Its working perfectly now

Sincerely
qrt123