Re: customizing "order by" question

Michael Stassen Fri, 12 Dec 2003 21:01:03 -0800

Chuck Gadd wrote:

Brandyn Riffle wrote:

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.

I'm also assuming you have a CHAR(3) month column. I'll further pretend your table is named "events_table", with columns "year" and "month", so you should replace those with the correct names in what follows (to apply to your table). If you have good reason to keep the month column, you may wish to build the sort order into it by using enum, like this:

ALTER TABLE events_table
MODIFY month
ENUM('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC');

The beauty of this is that the enum column returns the given strings in string context, but will return 1 through 12 in numeric context. So,

SELECT * from events_table ORDER BY year, month

would display the 3 letter string in the month column of the output, but would sort according to the numerical position in the list.

See <http://www.mysql.com/doc/en/ENUM.html> for details.

Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: customizing "order by" question

Reply via email to