Advanced SQL query. Top 12 from each category (MYSQL)

Question

I have a MYSQL5 database and PHP 5. I need a query for a games websites index page that only selects the first 12 from each category of games. Here is what I have so far.

$db->query("SELECT * FROM  `games` WHERE status = 'game_published'  AND `featured` =  '1' ORDER BY `category`");

The php code then groups games of the same category together and displays them. But yeah it doesn't limit the number of games from each category like I want.

Here is exactly what the structure of the table looks like: i49.tinypic.com/aysoll.png

Here is a blog post which sounds like what I am trying to do: http://www.e-nformation.net/content/view/title/MySQL+Top+N+in+each+group+(group+inner+limit) But I can't make sense of it.

Any help is appreciated.

Answer 1

How about this?

SELECT * FROM (
    SELECT
       games.*,
       @rn := CASE WHEN @category=category THEN @rn + 1 ELSE 1 END AS rn,
       @category := category
    FROM games, (SELECT @rn := 0, @category := NULL) AS vars
    WHERE status = 'game_published' AND featured = '1'
    ORDER BY category
) AS T1
WHERE rn <= 12