Andy >I want a single SQL query that will return the latest >ranking for each game:
See "Within-group aggregates" at http://www.artfulsoftware.com/infotree/queries.php.
PB ----- Andy Sy wrote:
Consider the ff. table: +--------+------+----------+ | game | rank | date | +--------+------+----------+ | GTA | 11 | 20081001 | +--------+------+----------+ | SPORE | 1 | 20081103 | +--------+------+----------+ | SPORE | 2 | 20091001 | +--------+------+----------+ | SINSOL | 8 | 20081011 | +--------+------+----------+ | SINSOL | 31 | 20080808 | +--------+------+----------+ | SPORE | 50 | 20090402 | +--------+------+----------+ | SINSOL | 11 | 20090104 | +--------+------+----------+ | GTA | 21 | 20080821 | +--------+------+----------+ | WOW | 1 | 20080922 | +--------+------+----------+ | WOW | 11 | 20081023 | +--------+------+----------+ | WOW | 15 | 20090106 | +--------+------+----------+ I want a single SQL query that will return the latest ranking for each game: +--------+------+----------+ | game | rank | date | +--------+------+----------+ | SPORE | 50 | 20090402 | +--------+------+----------+ | SINSOL | 11 | 20090104 | +--------+------+----------+ | GTA | 21 | 20080821 | +--------+------+----------+ | WOW | 15 | 20090106 | +--------+------+----------+ How do I go about it? The initial 'obvious' solution: select game,rank,max(date) from gametbl group by game DOESN'T work because the 'rank' value we get will not necessarily come from the same row holding the 'max(date)'!! Instead, you have to sort by date first in a subquery before applying the GROUP BY: select * from ( select * from gametbl order by date desc ) as t group by game This seems to work in MySQL but I do not trust this construction because it relies on ordering, which relational philosophy is supposed to not depend on. (Will this really safely work on all proper SQL implementations?) Another reason I don't like it is because it relies on a subquery and I was wondering if it is possible to do away with that. I can also imagine a solution relying on a self-join which does not depend on sorting, but which would require a surrogate primary key which I find even less elegant than relying a subquery. ========================= The Webmechs Webpress blog http://www.webmechs.com/webpress/ ------------------------------------------------------------------------ No virus found in this incoming message.Checked by AVG - www.avg.com Version: 8.0.238 / Virus Database: 270.11.49/2050 - Release Date: 04/09/09 10:27:00
