How do I break down MySQL query results into categories, each with a specific number of rows?
- by Mel
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!
