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

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

The following will order your data depending on both column in descending order   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

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

[mysql] Order a MySQL table by two columns

Examples related to mysql

Examples related to sql-order-by