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

Question

I have a MYSQL5 and PHP 5 database. I need a query for the index page of game websites in which only the first 12 of each category of games are selected. Here is what I still have.

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

Then the php code combines the games of the same category and displays them. But yes, this does not limit the number of games from each category as I want.

Here's what the table structure looks like: i49.tinypic.com/aysoll.png

Here is a blog post that sounds like what I'm trying to do: http://www.e-nformation.net/content/view/title/MySQL+Top+N+in+each+group+(group+inner+limit ) But I can not understand this.

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

Answer 2

you can use UNIONif we are not talking about millions 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 an array of categories in your PHP / ASP, you can generate this union on the fly.

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

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

Use it well ^^

Answer 3

, , . . , .

id, 10 , :

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
         )

where , 10 .

Answer 4

, . :

SELECT DISTINCT(category) FROM  `games`;

12 :

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

, - ( ), 12.

. , , .