order by more than 1 field

Discussion in 'MySQL' started by aayybb, Jul 21, 2009.

0
  1. #1
    I noticed my "order by customers_name, date" doesn't work.
    It works if I just have only "order by customers_name" or "order by date". But not when I have both "customers_name" and "date" in it.

    This is what I have

    SELECT * FROM list ORDER BY customers_name, date

    I even tried

    SELECT * FROM list GROUP BY customers_name ORDER BY date

    Still doesn't work.

    Any guess?
     
    aayybb, Jul 21, 2009 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    SELECT * FROM list ORDER BY customers_name, `date` DESC

    Make sure you put date in backticks. It is a reserved word, and should not be used for a column name. Also, what is the data type of the `date` field?
     
    jestep, Jul 21, 2009 IP
  3. aayybb

    aayybb Peon

    Messages:
    128
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Sorry, actually it is not "date". It is "date_purchased". It is a varchar.
     
    aayybb, Jul 21, 2009 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    You wont be able to sort by it. You would need to convert it to a date format before it could be sorted.

    What's the format of the data in the field? YYYY-MM-DD etc...
     
    jestep, Jul 21, 2009 IP
  5. aayybb

    aayybb Peon

    Messages:
    128
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Technically I am not sorting with date type. The data looks like this 09/Jun/2009:13:47:47. And I should be able to sort them by alphabetic order, right?
     
    aayybb, Jul 21, 2009 IP
  6. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #6
    The problem is that there is no logical alphabetical ordering for a date.

    Assuming the date is this: 09/Jun/2009:13:47:47. You would need to do something like:

    ORDER BY STR_TO_DATE(date_purchased,'%d/%m/%Y:%h:%i:%s') DESC;

    Also, if it's possible it would be a really good idea to convert your dates to a TIMESTAMP type column. This would allow you to properly sort and perform date related functions within MySQL.
     
    jestep, Jul 21, 2009 IP