On Thu, 6 Nov 2003, John W. Holmes wrote:

> I'd still like an answer to this question. Why is there a need for a
> separate table with scores?

 The log is a "snapshot" in time -- what was the "total points" at the time
 of the log entry.

 The score table is always the accurate current score.

> > Can it be done with a single query?  I can do it brilliantly easily in
> > code, but I like the challenge of doing it in SQL.
> Without knowing the exact table structure, maybe this'll work?
> mysql> select s.applicationid, s.teammemberid, l.points, sum(s.score),
> date from score s, log l where s.applicationid = l.applicationid group by
> l.points order by l.date desc limit 1;
> +---------------+--------------+--------+--------------+---------------------+
> | applicationid | teammemberid | points | sum(s.score) | date                |
> +---------------+--------------+--------+--------------+---------------------+
> |         19933 |           63 |     71 |           96 | 2003-08-14 17:43:22 |
> +---------------+--------------+--------+--------------+---------------------+
> 1 row in set (0.00 sec)

 That works for 1 row, but I want 30+ unique appid's and teammemberid's
 with the most recent date points.  Since some 2nd-to-last "promotions"
 occurred much later than the last promotion on others, ordering by date
 doesn't help either.

 Basically I need a report that says:

    When comparing the last Promotion log entry on table log, these are the
    applicationid and teammemberid combinations in which the sum of the
    items matching in the score table does not equal the points in the
    selected log entry.

Peter Beckman                                                  Internet Guy
[EMAIL PROTECTED]                             http://www.purplecow.com/

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to