List:General Discussion« Previous MessageNext Message »
From:Olexandr Melnyk Date:April 9 2009 5:54pm
Subject:Re: displaying a specific row within a "group by"
View as plain text  
Also: http://jan.kneschke.de/projects/mysql/groupwise-max

On Thu, Apr 9, 2009 at 8:46 PM, Peter Brawley
<peter.brawley@stripped> 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/
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