MySQL sorting issue

[omax]

Hello

I have a mysql database with columns id,productname,year,month,day .
I want to sort with year,month,day to have the more recent products first .

I'm using :
select id,year,month,day from table order by year desc,month desc,day desc
My problem is that I have records for month and day like '06' but also like '6'. ('05' and '5' ,etc)
And my query returns something like :

2007 6 6
2007 6 5
........
2007 06 06

I want it to be

2007 6 6
2007 06 06
..
2007 6 5
.........

Do you have any ideas? Thanks.

[phranque]

welcome to WebmasterWorld, omax!

try this:

SELECT id,year,LPAD(month,2,'0') AS month_2,LPAD(day,2,'0') AS day_2 FROM table ORDER BY year DESC,month_2 DESC,day_2 DESC

[edited by: phranque at 8:09 am (utc) on June 10, 2007]

[phranque]

[edit]accidental resubmit[/edit]

[omax]

Hi phranque!

It works excellent - Thank you.
Another small question - can I have problems if I'm using mysql words (like 'day','month' etc) for columns names?

[barns101]

They are reserved words in MySQL. You need to enclose them within backticks - `day`

[coopster]

True, you can use MySQL reserved words [dev.mysql.com] as an identifier if you quote them but you may want to just avoid using them as identifiers altogether. Set yourself a standard so that you won't bump into any words that are forbidden by standard SQL as column or table names. It makes coding and portability of your code that much easier.

[LifeinAsia]

I'd also suggest changing the year, month, and day fields to INT instead of VARCHAR. Then you don't have to worry about the 0 padding issue.

[mcibor]

Hi Omax and welcome to Webmasterworld!

You can even change day month year to one field `date` DATE;
then you won't have any problems with date sorting, nor checking (you will never have a date like 2007-29-87

And then sorting is easier as well, and day you can still retrieve with function DAY:

SELECT DAY(`date`);

More date and time functions you can find here:
[dev.mysql.com...]

Regards
Michal