Order a MySQL table by two columns

318

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)

+================+=============================+==============+
| article_rating | article                     | article_time |
+================+=============================+==============+
| 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.

mysql sql-order-by

Edit Report

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

answered 2012-04-20T09:19:12.103

Order a MySQL table by two columns

1 Answer

Your Answer