SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by month, day

    I have the following query:
    Code MySQL:
    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?
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    How about ordering by activity_date?
    Code:
    ORDER
        BY   activity_name, activity_date

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What format does the activity_date column have? DATE?

  5. #5
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guido, no it is a timestamp. Should it be date?
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by donboe View Post
    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.

  7. #7
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    make sure when you test a query that you test it outside of coldfusion first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •