Order by month, day

[donboe]

I have the following query:

SELECT
    A.activity_id
,   A.activity_name
,   A.activity_title
,   AD.activity_date
,   DATE_FORMAT( activity_date, '%d/%m') AS newDate
FROM
    activities A
INNER
  JOIN   activity_dates AD
    ON   AL.activity_id = AD.activity_id
ORDER
    BY   activity_name, MONTH(activity_date), DAY(activity_date)

With this I try to ORDER BY MONTH first and after that by DAY, but it keeps ordering by DAY

02/12, 03/12, 04/11, 04/12, 05/11, 06/11, 09/12, 10/12, 11/11

What am I doing wrong?

[guido2004]

How about ordering by activity_date?

ORDER
    BY   activity_name, activity_date

[donboe]

Somehow that is not working either. Maybe I should ask the same question in the Coldfusion forum since I use CF to create the list of dates

[guido2004]

What format does the activity_date column have? DATE?

[donboe]

Hi Guido, no it is a timestamp. Should it be date?

[guido2004]

Hi Guido, no it is a timestamp. Should it be date?

No timestamp is ok too. And ordering by it should put the activities in order of year-month-day

If the list of dates that CF gives you is out of order, then maybe you're correct in thinking that the problem may be a CF one.

[donboe]

Hi Guido, It is indeed. When I test it with just 1 activity_id it gives the right order, as soon as I come to this page where all activities are listed then the wrong ordering takes place.

[r937]

make sure when you test a query that you test it outside of coldfusion first