MySQL Group by category limit N from each category

Question

TABLE

id       title        category

1        hello1          1
2        hello2          2
3        hello3          1

Query

select  *
from    videos
where   category in
        (
        select  category
        from    videos
        group by 
               category
        having 
               count(*) < 3
ORDER BY RAND()
        )

my goal is to get 2 titles from each category in a random order also I want to fetch records in this manner

category1
title1
title2

category2
title1
title2

score 2 · Accepted Answer · answered Jan 03 '15 at 09:27

The below query gives no more than two random rows from each category:

SELECT title, category
FROM (
  SELECT v.*,
     if( category = @last_cat,
         if( @last_cat:=category, @x:=@x+1,@x:=@x+1),
         if( @last_cat:=category, @x:=0,@x:=0)
     ) x
  FROM (SELECT @last_cat:=-9876, @x:=-91234) x,
       (SELECT * FROM videos ORDER BY category, rand()) v
) x
WHERE x < 2

demo: http://sqlfiddle.com/#!2/59cf9/8

score 1 · Answer 2 · edited May 23 '17 at 11:51

1

UPDATED

Please try this updated query (SQL Fiddle - http://sqlfiddle.com/#!2/de35bb/9):

select  videos.*
from    videos
where   
    (
        select  COUNT(vid.id)
        from    videos AS vid
        WHERE videos.category = vid.category
    ) <= 2
ORDER BY
  videos.category, RAND()

I've managed to find the solution on SO here: Using LIMIT within GROUP BY to get N results per group? in which answer points to the article How to select the first/least/max row per group in SQL where author describes a few techniques of performing such task. The above query was built upon an example provided in the second article, but there are other forms of solving this problem.

edited May 23 '17 at 11:51

Community

1
1

answered Jan 03 '15 at 06:48

this actually doesn't do any good :( the problem is I can't think of how to do this. the algorithm is more difficult than the coding lol – Mustafa M Jalal Jan 03 '15 at 06:52
Please find the working (at least I thought so) example here **http://sqlfiddle.com/#!2/4d46d4/5** . Maybe you could edit your answer to show the attempt in PHP code for performing the task to the end? – Jan 03 '15 at 07:05
it's weird, it's working fine on the link you gave me with exact same code! – Mustafa M Jalal Jan 03 '15 at 07:06
The code in the answer along with the one in SQL Fiddle differs only slightly from the one you gave as your attempt - the differences are: `ORDER BY` is performed in the topmost query ; `ORDER BY category,` was added to get the results you marked as expected. So I think you were on a good path for the solution. – Jan 03 '15 at 07:09
oh i get it, it's grabbing titles with less than 3 cats so if you only add one more car '1' it will not work – Mustafa M Jalal Jan 03 '15 at 07:10
must say that for now I have no other idea how to build the query - hope you'll find the solution - I'll continue to try also. Regards. – Jan 03 '15 at 08:30
thanks btw, i just need to plan it on piece of paper it's too late now – Mustafa M Jalal Jan 03 '15 at 08:31

MySQL Group by category limit N from each category

2 Answers2