displaying a specific row within a group by
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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: displaying a specific row within a group by
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
Re: displaying a specific row within a group by
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
