Limiting groups with a query

Question

Please take a look at the following SQL Fiddle.

http://sqlfiddle.com/#!2/f7939/3

You will see my query of:

SELECT sub.entry_id, sum(jud.stage), sub.category_id, sub.member_group
FROM exp_judging AS jud
LEFT JOIN exp_submissions AS sub ON jud.rel_id = sub.id
WHERE jud.rel_id = jud.rel_id
GROUP BY jud.rel_id
ORDER BY 3 ASC
LIMIT 10

This is adding all rows together that have the same rel_id, and then outputting them.

I need to output only the top x number of each category, for now lets say 3 from each category.

So as an example, the results from my SQL Fiddle would be:

Category 1: (entry_id in order of score) 10, 11, 16

Category 2: 13, 12, 14

How can I achieve this?

Answer 1

While I'm sure there has to be a cleaner solution, you could introduce a row number using user defined variables to achieve this:

SELECT *
FROM (
  SELECT *,
    @rn:=IF(@prevCat=category_id, @rn + 1,1) rn,
    @prevCat:=category_id
  FROM (
    SELECT sub.entry_id, 
      sum(jud.stage), 
      sub.member_group,
      sub.category_id
    FROM exp_judging AS jud
      LEFT JOIN exp_submissions AS sub ON jud.rel_id = sub.id
    GROUP BY jud.rel_id
    ORDER BY sub.category_id, sum(jud.stage) DESC, sub.entry_id
  ) T
    JOIN (SELECT @rn:=0, @prevCat:=0) T2
) T
WHERE rn <= 3

• Updated SQL Fiddle Demo