MetaChat - MySQL question

MetaChat is an informal place for MeFites to touch base and post, discuss and chatter about topics that may not belong on MetaFilter. Questions? Check the FAQ. Please note: This is important.

18 September 2007

MySQL question →[More:]I need to return the results from a SELECT sorted twice, but I'm not sure how to do it correctly.

First it needs to be ordered by month, so it would list all of the September entries before the October entries. Then, it would need to sort them by date, so within the September entries the 13th would come before the 26th...

I've got the first part done...

ORDER BY `eventtable`.`month_of_event` ASC

post by: drezdn at: 13:49 | 6 comments

Haven't used MySQL, but if it's standard SQL, you'd just add a comma followed by your day column.

ORDER BY table.column,

table.othercolumn

posted by mike9322 18 September | 13:55

thanks mike9322, that did it.

posted by drezdn 18 September | 14:10

Ideally, you shouldn't really have separate fields for months and dates. You should have a single DATE type field. Then you could just do "ORDER BY table.date ASC" rather than "ORDER BY table.year ASC, table.month ASC, table.day ASC" (and there will be various other advantages to having a single date field). If you need to get the month, just use the function MONTH() in your SQL.

posted by matthewr 18 September | 14:11

seconding matthewr.

posted by deadcowdan 18 September | 14:54

thanks for mentioning the date field. I didn't think of it when I made the original table. I'm going to add a date field and then use some PHP to add that info for all the records I currently have.

posted by drezdn 18 September | 15:08

If the day/month/year are all numeric you can do:

UPDATE table SET date=CONCAT(year,'-',month,'-',day);

posted by cillit bang 18 September | 15:19

← No Internet for old memes || Guineas! OMG! →

HOME || REGISTER || LOGIN