List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 9 2009 5:46pm
Subject:Re: displaying a specific row within a "group by"
View as plain text  
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
>
>   

Thread
displaying a specific row within a "group by"Andy Sy9 Apr
  • Re: displaying a specific row within a "group by"Peter Brawley9 Apr
    • Re: displaying a specific row within a "group by"Olexandr Melnyk9 Apr