donboe — 2011-11-03T05:02:59-04:00 — #1
I have the following query:
, DATE_FORMAT( activity_date, '%d/%m') AS newDate
JOIN activity_dates AD
ON AL.activity_id = AD.activity_id
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 — 2011-11-03T05:11:17-04:00 — #2
How about ordering by activity_date?
BY activity_name, activity_date
donboe — 2011-11-03T05:33:00-04:00 — #3
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 — 2011-11-03T05:58:19-04:00 — #4
What format does the activity_date column have? DATE?
donboe — 2011-11-03T06:13:51-04:00 — #5
Hi Guido, no it is a timestamp. Should it be date?
guido2004 — 2011-11-03T06:28:00-04:00 — #6
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 — 2011-11-03T06:38:07-04:00 — #7
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 — 2011-11-03T06:40:42-04:00 — #8
make sure when you test a query that you test it outside of coldfusion first