What I'm trying to do is sort by a column with by pre-set criteria; I've a political database with events with columns for the year, month, day, and event. I'd like to order by months, (e.g. JAN, FEB, MAR, etc...) after sorting by year. The sorting by year part was easy... the months are another matter.
First, the correct solution would probably be to store your event date in an actual DateTime column. Then MySql would know how to sort it properly. And you could still get the seperate pieces out easily. For example if you had a EventDate column of type DateTime, then you could do
select year(EventDate) as Year, monthname(EventDate) as month, dayofmonth( EventDate) as Day, dayofweek(EventDate) as WeekDay from MyTable and you'd get back columns like:
|year|month |day|WeekDay| |2003|December| 12|Friday |
------------------------------
So, that would be the RIGHT way to do it. But, you can make do with what you've got as well.
I'm assuming you've got a Month field that contains 3 letter month abbreviations like JAN,FEB,MAR,APR,MAY, etc. Adjust the actual abbreviation/spelling as needed:
select case month when 'JAN' then 1 when 'FEB' then 2 when 'MAR' then 3 when 'APR' then 4 when 'MAY' then 5 when 'JUN' then 6 when 'JUL' then 7 when 'AUG' then 8 when 'SEP' then 9 when 'OCT' then 10 when 'NOV' then 11 when 'DEC' then 12 else 13 end as MonthNum, month,day,year from test2 order by year,monthnum
The case statement here converts the month abbrev. into numbers which will sort correctly.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
