Also: http://jan.kneschke.de/projects/mysql/groupwise-max
On Thu, Apr 9, 2009 at 8:46 PM, Peter Brawley <peter.braw...@earthlink.net> wrote: > 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 >> >> > -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
