Posted by Andy Hassall on 07/02/05 00:39

On Fri, 1 Jul 2005 16:28:37 -0400, "Jim Moseby" <nospam@dontspam.me> wrote:

>I have tried and tried and I can't figure this one out:
>
>I have a table that contains player names and scores for an online game:
>
>| Player_Name | Score | DateTime |
>+--------------+---------+--------------------+
>| Bill | 1234.56 | 2005-02-01 15:36:00|
>| Mary | 2345.67 | 2005-02-01 15:32:02|
>| Bill | 1133.34 | 2005-02-02 10:25:33|
>| Fred | 1113.89 | 2005-02-02 11:22:44|
>
>etc....
>
>This table gets updated every time the player's score changes with a new
>record. This makes for a nice progress graph over time.
>
>I want to generate a "Gains and Slides" display showing the 5 players who's
>scores have increased the most, and the 5 players who's scores have
>decreased the most over a 7 day period.
>
>Not all players will have an entry for every day.
>
>So what I want to do, essentially, is get everyone's score that is nearest
>to midnight 7 days ago, and compare them with everyone's most recent score
>(if they have an entry in the last 7 days), and generate a difference:
>
>| Player_Name | Diff |
>+--------------+---------+
>| Bill | 26.22 |
>| Mary | -102.88 |
>| Fred | 221.10 |
>
>I just can't seem to get my head around this one. :-/ Anyone care to take
>it on?

Easier to answer if you post:

(1) Runnable DDL (CREATE TABLE etc.) to create the tables
(2) Runnable SQL (INSERT etc.) to set up some sample data
(3) The database you're using

This should be relatively easy for a database that supports subqueries - are
you using one that does?

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Back to original message]