SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Order by MonthName

    Hi all, I need your appreciated help.

    This is my mysql table:
    PHP Code:
    mysqlSELECT
        DATE_FORMAT
    (
            
    STR_TO_DATE(myDate'%d/%m/%Y'),
            
    '%b, %Y'
        
    ) AS `MonthName`,
        
    myDate AS `MyDate`
    FROM
        tbl_myDate
    GROUP BY
        
    `MyDate`
    ORDER BY
        
    `MyDateDESC;
    +-----------+------------+
    MonthName MyDate     |
    +-----------+------------+
    Dec2012 01/12/2012 |
    Dec2011 01/12/2011 |
    Dec2010 01/12/2010 |
    Dec2009 01/12/2009 |
    Dec2008 01/12/2008 |
    Nov2012 01/11/2012 |
    Nov2011 01/11/2011 |
    Nov2010 01/11/2010 |
    Nov2009 01/11/2009 |
    Nov2008 01/11/2008 |
    Oct2012 01/10/2012 |
    Oct2011 01/10/2011 |
    Oct2010 01/10/2010 |
    Oct2009 01/10/2009 |
    Oct2008 01/10/2008 |
    Sep2012 01/09/2012 |
    Sep2011 01/09/2011 |
    Sep2010 01/09/2010 |
    Sep2009 01/09/2009 |
    Sep2008 01/09/2008 |
    Aug2012 01/08/2012 |
    Aug2011 01/08/2011 |
    Aug2010 01/08/2010 |
    Aug2009 01/08/2009 |
    Aug2008 01/08/2008 |
    Jul2012 01/07/2012 |
    Jul2011 01/07/2011 |
    Jul2010 01/07/2010 |
    Jul2009 01/07/2009 |
    Jul2008 01/07/2008 |
    Jun2012 01/06/2012 |
    Jun2011 01/06/2011 |
    Jun2010 01/06/2010 |
    Jun2009 01/06/2009 |
    Jun2008 01/06/2008 |
    May2012 01/05/2012 |
    May2011 01/05/2011 |
    May2010 01/05/2010 |
    May2009 01/05/2009 |
    May2008 01/05/2008 |
    Apr2012 01/04/2012 |
    Apr2011 01/04/2011 |
    Apr2010 01/04/2010 |
    Apr2009 01/04/2009 |
    Apr2008 01/04/2008 |
    Mar2012 01/03/2012 |
    Mar2011 01/03/2011 |
    Mar2010 01/03/2010 |
    Mar2009 01/03/2009 |
    Mar2008 01/03/2008 |
    Feb2012 01/02/2012 |
    Feb2011 01/02/2011 |
    Feb2010 01/02/2010 |
    Feb2009 01/02/2009 |
    Feb2008 01/02/2008 |
    Jan2013 01/01/2013 |
    Jan2012 01/01/2012 |
    Jan2011 01/01/2011 |
    Jan2010 01/01/2010 |
    Jan2009 01/01/2009 |
    Jan2008 01/01/2008 |
    +-----------+------------+
    61 rows in set 
    I need this order in output, can you help me?
    Thanks in advance.
    PHP Code:
    +-----------+------------+
    MonthName MyDate     |
    +-----------+------------+
    Jan2013 01/01/2013 |
    Dec2012 01/12/2012 |
    Nov2012 01/11/2012 |
    Oct2012 01/10/2012 |
    Sep2012 01/09/2012 |
    Aug2012 01/08/2012 |
    Jul2012 01/07/2012 |
    Jun2012 01/06/2012 |
    May2012 01/05/2012 |
    Apr2012 01/04/2012 |
    Mar2012 01/03/2012 |
    Feb2012 01/02/2012 |
    Jan2012 01/01/2012 |

    ...
    ...
    ...

    +-----------+------------+ 

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    ORDER BY
        YEAR(`MyDate`) DESC
      , MONTH(`MyDate`) DESC

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    guido, his `Mydate` column is a varchar (because he needs to use STR_TO_DATE on it) and therefore you can't apply date functions to it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,868
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    So what is needed is to add another field that uses STR_TO_DATE(myDate, '%Y-%m-%d') on the same date and then use that field in the order by instead.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Or change the column type into DATE

  6. #6
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    thank you all for help.
    PHP Code:
    GROUP BY
        myDate
    ORDER BY
        YEAR 
    (
            
    STR_TO_DATE(myDate'%d/%m/%Y')
        ) 
    DESC,
        
    MONTH (
            
    STR_TO_DATE(myDate'%d/%m/%Y')
        ) 
    DESC

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    much simpler would be
    Code:
    ORDER 
        BY STR_TO_DATE(myDate, '%d/%m/%Y') DESC
    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
  •