Years are stored as smallint(4) Months are stored as tinytext
converting to datetime might have been a good idea earlier, but I've over 3000 entries, so if I can avoid that I'd like to... I'm not above creating another column to correlate months to integers, and sort by that if that would work...
Do you mean they're stored as the string values 'JAN', 'FEB', 'MAR', etc?
If so, the natural sort order is lexical, which isn't what you want. However, you can use FIELD() to map an arbitrary set of values onto a given numeric order:
ORDER BY FIELD(month,'JAN','FEB','MAR',...,'NOV','DEC')
will map month values onto the numbers 1 to 12 and sort them numerically.
http://www.mysql.com/doc/en/String_functions.html
From: Paul DuBois <[EMAIL PROTECTED]> To: "Brandyn Riffle" <[EMAIL PROTECTED]>, [EMAIL PROTECTED] Subject: Re: customizing "order by" question Date: Fri, 12 Dec 2003 20:44:04 -0600
At 20:36 -0500 12/12/03, Brandyn Riffle wrote:If anyone has any suggestions, they would be greatly appreciated. I've searched though my resources and online, and perhaps my newbie frustration is making me overlook something simple.
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.
We cannot answer your question, because you have stated your requirements without telling us how your data values actually are represented. I suppose the years are stored as integers, but how are months stored?
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
