MySQL limit results per category

Question

Each item in c_data is in a category/section. I would like to limit how many items are displayed per category, rather than limiting the total number of items retrieved. Obviously if I add something like "limit 20" to the query, it will only fetch 20 results in total, rather than 20 results per category.

   SELECT cm.id,
                cm.title AS cmtitle,
                cm.sectionid,
                cm.type AS cmtype,
                cd.id,
                cd.time,
                cd.link,
                cd.title,
                cd.description,
                cd.sectionid AS sectionid
      FROM c_main AS cm
      JOIN c_data AS cd ON cd.sectionid=cm.sectionid
     WHERE cd.sectionid=cm.sectionid 
     ORDER by id ASC

The field with the category is "sectionid".

Answer 1

The answers to this previous post should help you to solve that problem.

EDIT:

It should work with using row numbers.

I have not tried it, but this should work:

set @section = '';
set @num  = 1;

SELECT y.*
FROM
(
    SELECT
      x.*, 
      @num := if(@section = sectionid, @num + 1, 1) as row_number,
      @section := sectionid
    FROM
    (
        SELECT 
          cm.id AS cm_id,
          cm.title AS cmtitle,
          cm.sectionid,
          cm.type AS cmtype,
          cd.id AS cd_id,
          cd.time,
          cd.link,
          cd.title,
          cd.description
        FROM c_main AS cm
        JOIN c_data AS cd ON ( cd.sectionid=cm.sectionid )
        ORDER by cd.sectionid ASC, cm.id ASC
    ) x
) y
WHERE y.row_number <= 20;

Answer 2

MySQL doesn't have any ranking functionality, but you can use a variable to create a psuedo row number.

Use:

SELECT x.*
  FROM (SELECT cm.id,
               cm.title AS cmtitle,
               cm.sectionid,
               cm.type AS cmtype,
               cd.id AS cd_id,
               cd.time,
               cd.link,
               cd.title,
               cd.description,
               cd.sectionid AS cd_sectionid,
               CASE
                 WHEN @sectionid != cm.sectionid THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1
               END AS rank,
               @sectionid := cm.sectionid
          FROM C_MAIN cm,
               C_DATA cd,
               (SELECT @rownum := 0, @sectionid := NULL) r
         WHERE cm.sectionid = cd.sectionid
      ORDER BY cm.sectionid) x
 WHERE x.rank <= 20
ORDER BY id