# Order a MySQL table by two columns

## The Question :

239 people think this question is useful

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


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.

509 people think this answer is useful

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

ORDER BY article_rating DESC, article_time DESC



37 people think this answer is useful
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 ↓



but consider:

↓ primary sort                         secondary sort ↓
1.  50 | This article rocks, too     | Feb 4, 2009    4.



13 people think this answer is useful
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

8 people think this answer is useful

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



3 people think this answer is useful

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

ORDER BY article_rating DESC, article_time DESC