Search code examples
mysqlsqlgroup-concattop-n

MySQL query: How can I find top 5 images in each brand ?

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 get following in return :

brand_id|image_ids                
--------|--------------------------
8       |9                       
35      |2829,2831,2832,2894     
28      |2960,2961,3041,2965,2959
Solution

  • Use SUBSTRING_INDEX

    SELECT
  brand_id,
  SUBSTRING_INDEX(GROUP_CONCAT(image_id SEPARATOR ','),',',5) AS image_ids
FROM Table1
GROUP BY brand_id

    fiddle