Order a MySQL table by two columns

Question

How do I sort a MySQL table by two columns     What I want are articles sorted by highest ratings first  then most recent date   As an example  this would be a sample output  left   is the rating  then the article title  then the article date    50   This article rocks            Feb 4  2009 35   This article is pretty good   Feb 1  2009 5    This Article isn t so hot     Jan 25  2009   The relevant SQL I m using is   ORDER BY article rating  article time DESC   I can sort by one or the other  but not both

User · Answer

The following will order your data depending on both column in descending order   ORDER BY article rating DESC  article time DESC

User · Answer

This maybe help somebody who is looking for the way to sort table by two columns  but in paralel way  This means to combine two sorts using aggregate sorting function  It s very useful when for example retrieving articles using fulltext search and also concerning the article publish date   This is only example  but if you catch the idea you can find a lot of aggregate functions to use  You can even weight the columns to prefer one over second  The function of mine takes extremes from both sorts  thus the most valued rows are on the top   Sorry if there exists simplier solutions to do this job  but I haven t found any   SELECT   id     text     date   FROM         SELECT      k  id        k  text        k  date        k  match order id         row     row   1 as  date order id       FROM               SELECT          t  id            t  text            t  date             row     row   1 as  match order id           FROM                       SELECT               art id  AS  id                 text    AS  text                 date    AS  date                MATCH   text   AGAINST   string  AS  match               FROM int art fulltext              WHERE MATCH   text   AGAINST   string IN BOOLEAN MODE               LIMIT 0 101            t                        SELECT  row    0            r          ORDER BY  match  DESC        k                SELECT  row    0        l      ORDER BY k  date  DESC      s  ORDER BY  1  match order id  1  date order id   DESC

User · Answer

ORDER BY article rating  article time DESC   will sort by article time only if there are two articles with the same rating  From all I can see in your example  this is exactly what happens     primary sort                         secondary sort   1   50   This article rocks            Feb 4  2009    3  2   35   This article is pretty good   Feb 1  2009    2  3   5    This Article isn t so hot     Jan 25  2009   1    but consider     primary sort                         secondary sort   1   50   This article rocks            Feb 2  2009    3  1   50   This article rocks  too       Feb 4  2009    4  2   35   This article is pretty good   Feb 1  2009    2  3   5    This Article isn t so hot     Jan 25  2009   1

User · Answer

Default sorting is ascending  you need to add the keyword DESC to both your orders   ORDER BY article rating DESC  article time DESC

User · Answer

ORDER BY article rating ASC   article time DESC   DESC at the end will sort by both columns descending  You have to specify ASC if you want it otherwise

[mysql] Order a MySQL table by two columns

Examples related to mysql

Examples related to sql-order-by