Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Order a MySQL table by two columns

People also ask

Can we use ORDER BY for 2 columns?

Discussion: If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY . This clause comes at the end of your SQL query.

How does ORDER BY multiple columns work?

If you specify multiple columns, the result set is sorted by the first column and then that sorted result set is sorted by the second column, and so on. The columns that appear in the ORDER BY clause must correspond to either column in the select list or columns defined in the table specified in the FROM clause.

How do I SELECT two columns in a table in MySQL?

To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.

What is ORDER BY 2 in SQL?

Sort by ordinal positions of columns But instead of specifying the column names explicitly, it uses the ordinal positions of the columns: SELECT first_name, last_name FROM sales. customers ORDER BY 1, 2; In this example, 1 means the first_name column and 2 means the last_name column.


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

ORDER BY article_rating DESC, article_time DESC

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.

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


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

The following will order your data depending on both column in descending order.

ORDER BY article_rating DESC, article_time DESC