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.
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