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












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