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
>
>
