299

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.

nabster
  • 1,561
  • 2
  • 20
  • 32

4 Answers4

612

Default sorting is ascending, you need to add the keyword DESC to both your orders:

ORDER BY article_rating DESC, article_time DESC
mthurlin
  • 26,247
  • 4
  • 39
  • 46
  • Odd. When I have two columns, the name and total and want to order alphabetically by name and DESC by total, then I see only, that it was ordered by name, but not by total – Eugene May 03 '12 at 09:20
  • I been hacking with (-1)*field1, field2 for no reason on numeric fields...thanks. – Asad Hasan Jun 11 '14 at 22:50
  • hahaha .... not is a good query, because if you try, never can reorder the "empty" values category... nice try in 2009 .. but in 2015 don't work correctly ;), The correct is "3" or "2" sub query –  Feb 04 '16 at 15:38
  • Such query not working in my case.. Tn this case, I dont get sorting for City select distinct City, Country from customers order by Country desc, City desc; – PAA Sep 15 '16 at 06:55
  • 9
    I don't know why this is checked as answer yet it isn't. It sorts by the first column then the second but not both at the same time. – aidonsnous Apr 27 '17 at 13:14
  • 2
    @aidonsnous which is exactly what is required in this instance? Sorting by 1 column first and then another column is the solution – ArabianMaiden Jan 12 '22 at 22:45
45
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.
Tomalak
  • 332,285
  • 67
  • 532
  • 628
26
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

GilZ
  • 6,418
  • 5
  • 30
  • 40
Learning
  • 8,029
  • 3
  • 35
  • 46
8

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
Jiri Fornous
  • 402
  • 4
  • 10