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

To use the technique from the posts you mention, you need a way to order the games. They're using article date. Then they select the number of older articles for that company, and say there can't be more than three.

If your games table has an auto-increment column called id, you can select the top 10 games per category like:

SELECT   *
FROM     games g1
WHERE    status = 'game_published'  
         AND featured = '1' 
         AND 10 > 
         (
         SELECT   COUNT(*)
         FROM     games g2
         WHERE    g2.status = 'game_published'  
                  AND g2.featured = '1' 
                  AND g1.category = g2.category
                  AND g2.id > g1.id
         )

The where condition says that there can't be more than 10 rows with the same category and a higher ID.

Answer 2

There may be a more elegant solution, but you can just execute a query for each category. First get a list of categories:

SELECT DISTINCT(category) FROM  `games`;

Then take each of the results and query for 12 rows:

SELECT * FROM games WHERE status = 'game_published'  
AND `featured` =  '1' AND `category` = $category LIMIT 12;

Of course you need to add some kind of ranking row (and order by it) to get the top 12.

Note: There may be a way to do this with a single query, but it escapes me at the moment.

Answer 3

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

Answer 4

you could use UNION, if we are not talking about million of types...

pseudoSQL:

(SELECT * FROM table WHERE condition AND category = 'action' ORDER BY id LIMIT 10)
UNION
(SELECT * FROM table WHERE condition AND category = 'action' ORDER BY id LIMIT 10)
UNION
(SELECT * FROM table WHERE condition AND category = 'action' ORDER BY id LIMIT 10)

If you have array of categories in your PHP/ASP, you can generate this union on the fly.

More: http://dev.mysql.com/doc/refman/5.0/en/union.html

EDIT: Here's probably most useful resource: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Use it well ^^