How do I break down MySQL query results into categories, each with a specific number of rows?

Question

Hello,

Problem: I want to list n number of games from each genre (order not important)

The following MySQL query resides inside a ColdFusion function. It is meant to list all games under a platform (for example, list all PS3 games; list all Xbox 360 games; etc...). The variable for PlatformID is passed through the URL. I have 9 genres, and I would like to list 10 games from each genre.

        SELECT
            games.GameID AS GameID,
            games.GameReleaseDate AS rDate,                
            titles.TitleName AS tName,
            titles.TitleShortDescription AS sDesc,
            genres.GenreName AS gName,
            platforms.PlatformID,
            platforms.PlatformName AS pName,
            platforms.PlatformAbbreviation AS pAbbr
        FROM
            (((games join titles on((games.TitleID = titles.TitleID))) join genres on((genres.GenreID = games.GenreID))) join platforms on((platforms.PlatformID = games.PlatformID)))
        WHERE
            (games.PlatformID = '#ARGUMENTS.PlatformID#')
        ORDER BY
            GenreName ASC,
            GameReleaseDate DESC

Once the query results come back I group them in ColdFusion as follows:

<cfoutput query="ListGames" group="gName"> (first loop which lists genres)
#ListGames.gName#
      <cfoutput> (nested loop which lists games)
      #ListGames.tName#
      </cfoutput>
</cfoutput>

The problem is that I only want 10 games from each genre to be listed. If I place a "limit" of 50 in the SQL, I will get ~ 50 games of the same genre (depending on how much games of that genre there are). The second issue is I don't want the overload of querying the database for all games when each person will only look at a few.

What is the correct way to do this?

Many thanks!

Answer 1

You could do a Coldfusion query of queries, but I believe that the solution resides in MySQL. That way you have the best control over the results you get back and frankly MySQL will sort and group this type of data much faster than Coldfusion.

Either you could get fancy with some grouping, limit and possibly a subselect. Alternately you can split your select statements up into one query per genre. I don't know how many people you'll have viewing this database, but 9 queries that each return 10 results is kids play for the database server. I'm sure it would be preferable to have the 90 results return in a single query for simplicity of output. That can be done, but the SELECT statement is going to be much more complicated. If you go that route, I'd recommend a stored procedure.

Answer 2

I'm a little rusty so I'm sure there's some bugs and syntax errors in here. This stored procedure should create a temporary table with the data you're looking for

CREATE PROCEDURE topbygenre(num INT, platformID INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a INT;
  DECLARE cur CURSOR FOR SELECT GenreID FROM genres;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  DECLARE first INT DEFAULT 1;

  OPEN cur;

  REPEAT
    FETCH cur INTO a;
    IF NOT done THEN
      IF first THEN
        CREATE TEMPORARY TABLE TopGames
        SELECT
          games.GameID AS GameID,
          games.GameReleaseDate AS rDate,                
          titles.TitleName AS tName,
          titles.TitleShortDescription AS sDesc,
          genres.GenreName AS gName,
          platforms.PlatformID,
          platforms.PlatformName AS pName,
          platforms.PlatformAbbreviation AS pAbbr
        FROM
          (((games join titles on((games.TitleID = titles.TitleID))) join genres on((genres.GenreID = games.GenreID))) join platforms on((platforms.PlatformID = games.PlatformID)))
        WHERE
          (games.PlatformID = platformID)
          AND games.GenreID = a
        ORDER BY
          GenreName ASC,
          GameReleaseDate DESC
        LIMIT num;
        SET first = 0;
      ELSE
        INSERT INTO TopGames
        SELECT
          games.GameID AS GameID,
          games.GameReleaseDate AS rDate,                
          titles.TitleName AS tName,
          titles.TitleShortDescription AS sDesc,
          genres.GenreName AS gName,
          platforms.PlatformID,
          platforms.PlatformName AS pName,
          platforms.PlatformAbbreviation AS pAbbr
        FROM
          (((games join titles on((games.TitleID = titles.TitleID))) join genres on((genres.GenreID = games.GenreID))) join platforms on((platforms.PlatformID = games.PlatformID)))
        WHERE
          (games.PlatformID = platformID)
          AND games.GenreID = a
        ORDER BY
          GenreName ASC,
          GameReleaseDate DESC
        LIMIT num;
      END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur;
END

Then execute tihs every time you need the data:

topbygenre(10, '#ARGUMENTS.PlatformID#');
select * from TopGames;

Answer 3

In Oracle I would probably play with Groups HAVING(ROWNUM) <= 10 but I do not think MYSQL supports that.

If you have too many rows to just pull all games down and iterate in group logic then I agree with the above poster that breaking it out into separate queries while not optimum may be fine if you are not running a lot of concurrent users and use a bit of caching.

My first try would be something like this - while not tested may give you some ideas. so let me know if this is close - at least it is simple (note the maxrows="10" and check that it respects the Order By)...

 <cfquery name="Genres" datasource="#Application.DB#" cachedWithin="#createTimeSpan(0,0,30,0)#">
 SELECT 
   Distinct (GenreName) as UniqueGenreName
 FROM
   games join genres on(genres.GenreID = games.GenreID)
 WHERE
   games.PlatformID = <CFQUERYPARAM VALUE="#ARGUMENTS.PlatformID#" CFSQLTYPE="CF_SQL_VARCHAR">
 ORDER BY
   GenreName
 </cfquery>

 <!--- Table header here --->

 <cfloop query ="Genres">

   <cfquery name="SubGenres" datasource="#Application.DB#" maxrows="10" cachedWithin="#createTimeSpan(0,0,30,0)#">
    SELECT
             games.GameID AS GameID,
             games.GameReleaseDate AS rDate,                
             titles.TitleName AS tName,
             titles.TitleShortDescription AS sDesc,
             genres.GenreName AS gName,
             platforms.PlatformID,
             platforms.PlatformName AS pName,
             platforms.PlatformAbbreviation AS pAbbr
         FROM
             (((games join titles on((games.TitleID = titles.TitleID))) join genres on((genres.GenreID = games.GenreID))) join platforms on((platforms.PlatformID = games.PlatformID)))
         WHERE
             games.PlatformID = <CFQUERYPARAM VALUE="#ARGUMENTS.PlatformID#" CFSQLTYPE="CF_SQL_VARCHAR"> AND genres.GenreName = <CFQUERYPARAM VALUE="#UniqueGenreName#" CFSQLTYPE="CF_SQL_VARCHAR">
         ORDER BY
             GameReleaseDate DESC
    </cfquery>

    <cfoutput query ="SubGenres">
    <!--- Table rows here --->
    </cfoutput>

 </cfloop>

 <!--- Table footer here --->

Answer 4

You say you want 10 games from each genre, but don't specify which 10 games you want. I assume from the ordering (GenreName, GameReleaseDate) that you want to display the 10 most recently released games.

It sounds like you want to use a SELECT TOP n - type clause. Someone else on SO asked about a database-agnostic method for selecting the top n records.

Using the answer from that question, you could try this:

    SELECT
        games.GameID AS GameID,
        games.GameReleaseDate AS rDate,                
        titles.TitleName AS tName,
        titles.TitleShortDescription AS sDesc,
        genres.GenreName AS gName,
        platforms.PlatformID,
        platforms.PlatformName AS pName,
        platforms.PlatformAbbreviation AS pAbbr
    FROM
        (((games join titles on((games.TitleID = titles.TitleID))) join genres on((genres.GenreID = games.GenreID))) join platforms on((platforms.PlatformID = games.PlatformID)))
    WHERE
        (games.PlatformID = '#ARGUMENTS.PlatformID#')
        AND (SELECT COUNT(*) FROM games AS NewerGames where NewerGames.PlatformID = games.PlatformID AND games.GenreID=NewerGames.GenreID AND (NewerGames.GameReleaseDate < Games.GameReleaseDate OR (NewerGames.GameReleaseDate = Games.GameReleaseDate AND NewerGames.GameID > games.GameID))) <= 10
    ORDER BY
        GenreName ASC,
        GameReleaseDate DESC

I haven't tested it but I'm pretty sure it should work. Give it a try, if it works it's a simple answer to the problem.

Note that one of the criteria -- NewerGames.GameID > games.GameID is there solely to avoid the problem of there being more than one game with the same release date. In these cases, the game that has a higher id (and therefore, in theory, slightly "younger") will get filtered out last.

Answer 5

HTH http://blog.aharbick.com/2006/09/mysql-groupwise-limiting.html