Order By

mickfitz

Hello

Still new but learning fast :-)

I use this to stack the way data is displayed in one of my 'simple' databases:

ORDER BY month,day

It works OK, but the months are sorted in alphabetical order. The months are spelt out in full (January, February etc).

Can I add something near the ORDER BY to make it stack in correct month order.

Any Advise will be appreciated

TIA ... Mick

Tewl

my only suggestion would be to use the month's number instead of the name

statrat

if possible, you could convert the column type to enum()
http://www.mysql.com/doc/en/ENUM.html
or a set
http://www.mysql.com/doc/en/SET.html

"ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.) For example, "a" sorts before "b" for ENUM("a", "b"), but "b" sorts before "a" for ENUM("b", "a"). The empty string sorts before non-empty strings, and NULL values sort before all other enumeration values. To prevent unexpected results, specify the ENUM list in alphabetical order. You can also use GROUP BY CONCAT(col) to make sure the column is sorted alphabetically rather than by index number. "

Thora_Fan

mickfitz, are you using an actual date/time type for your month and day fields? If so, ORDER BY month,day should work correctly. If you aren't using those types, I suggest doing so. It makes life so much easier once you learn how to manipulate them.

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#DATETIME

However, if I am wrong and just have misunderstood this post and the other replies, then ignore all this.

statrat

i thought he was using strings "January", "February", etc. and not date types.

i agree he should use date types, but he didn't ask, so i assumed he needed to do it the way he was doing it.

mickfitz

Hello

Thanks for the replies so far. I kinda guessed that there would be a more accepted method of using the date. But my hands are tied and I must use the full month names.

So is there a way of telling 'Order by' to stack the months:

January
February
March
April
May

etc etc

I'd love to suss this one

Mick

Thora_Fan

Do you have to use the month name inside the database? there are many functions that can be used to convert timestamp fields to readable formats.

mickfitz

Yes the months are listed in one column called 'month' the table is called 'venues'

Mick

driverdave

I always store dates as unix timestamps. That way I can always order them ASC or DESC without problems.

ahundiak

Assuming you are using mysql and thus do not have access to stored procedures then I don't think is any way to get sql to sort by month name.

However, You can load all the records retrieved by a query into an array, create a custom compare function and then use usort to sort them.