Geek asks and answers

Mixing results of various categories sorted by score in MySQL

In my PHP application, I have a mysql article table that has the following columns:

article_id    articletext    category_id    score

Each article has a rating, which is calculated on the basis of how popular it is and belongs to a certain category (about 10 categories are available)

My question is: how can I fulfill a query that returns the highest typed articles, alternating them by category so that, if possible, articles of the same category are returned sequentially. For example, if the highest score has a score of: 100, the returned set will be something like this:

article_id    articletext    category_id    score
-----------------------------------------------------
142           <.....>        5              100
153           <.....>        3              97
119           <.....>        5              99
169           <.....>        2              93
121           <.....>        7              89
197           <.....>        2              92
.
.
.

() , , 10 ( 1 ), , PHP , .

? , MySQL

+3
3

Go 20. , , . , .

100 , 90% , 10 .

SQL Server, ...

, . 5 , . DML -, (, ). , ... .

, -, . .

, . , 5 . , 1 .

+1

. . , .

select * from (
(select @r:=@r+1 as rownum,article_id,articletext,category_id,score
from articles,(select @r:=0) as r
where category_id = 1
order by score desc limit 100000000) 
union all
(select @r1:=@r1+1,article_id,articletext,category_id,score
from articles,(select @r1:=0) as r
where category_id = 2
order by score desc limit 100000000)
union all
(select @r2:=@r2+1,article_id,articletext,category_id,score
from articles,(select @r2:=0) as r
where category_id = 3
order by score desc limit 100000000)
) as t
order by rownum,score desc
+1

Your naive decision is exactly what I will do.

0
source

More articles:


All Articles