Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Multiple ORDER BY trying to sort by follow up date.

    Hello and thanks in advance,

    I have a database table containing contact logs for our booking system, I want to be able to list all the logs and filter them firstly by Status and then order those by their follow up date

    I have tried a variety of the following but whilst is sorts by Status the dates seem to be in a random order. I've used -STR because some do not have dates and I want those to be put at the bottom of the order, and STR_TO_DATE becuase I'm using UK dates and a varchar field.

    sql = "SELECT * FROM contact_log ORDER BY CL_Status, -STR_TO_DATE(CL_FU_Date, '%d/%m/%Y') DESC"

    Any suggestions?

  • #2
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,910
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Is that a typo in your post or did you actually prepend a dash to the function call?

    Instead of using * to select all fields, you should be explicit and specify each field you need and in the order you want them returned.

    This is untested, but give it a try. (Don't forget to add the additional fields you need)
    Code:
    $sql = "SELECT CL_Status,
                   STR_TO_DATE(CL_FU_Date, '%d/%m/%Y') AS CL_FU_Date
            FROM contact_log
            ORDER BY CL_Status ASC,
                     CL_FU_Date DESC";


  •  

    Posting Permissions

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