mysql query, how can i get top 5 downloads with GROUP_CONCAT

group-concat, mysql, select, sql, top-n

Solution

Try this:

SELECT brand_id, GROUP_CONCAT(image_id) image_ids
FROM (SELECT brand_id, image_id, 
             IF(@lastBrandId = @lastBrandId:=brand_id, @Idx:=@Idx+1, @Idx:=0) rowNumber 
      FROM Table1, (SELECT @lastBrandId:=0, @Idx:=0) A
      ORDER BY brand_id, downloads DESC
     ) AS A
WHERE rowNumber < 5
GROUP BY brand_id

Problem

the following data in MySql table. ``` brand_id|image_id|downloads --------|--------|--------- 8 |9 |8 35 |2829 |4 28 |2960 |3 28 |2961 |3 28 |3041 |3 35 |2831 |3 28 |2965 |3 35 |2832 |3 28 |2959 |2 28 |2976 |2 35 |2894 |2 ``` How can i find top 5 images with `GROUP_CONCAT` function in each brand And i would like to following to return ``` brand_id|image_ids --------|-------------------------- 8 |9 35 |2829,2831,2832,2894 28 |2960,2961,3041,2965,2959 ``` The following query provide expected result ``` SELECT brand_id, SUBSTRING_INDEX(GROUP_CONCAT(image_id SEPARATOR ','),',',5) AS image_ids FROM Table1 GROUP BY brand_id ``` i have a large amount data approx 2MILION records in table and brand_id, image_id both are repeated with each user download so when i run query i got following error ``` MySql Error: %d line(s) were cut by GROUP_CONCAT() ``` I think the GROUP_CONCAT range exceeded, can you please provide solution so i have get only top 5 downloads in each brand so there not require SUBSTRING_INDEX function. Thank you

Original source