Re: query problem

On Sun, 1 May 2005 17:05:50 +0200, tw wrote:

>Hi,
>
>I have a problem to run a query like this scenario:
>
>i have players in a table who have scored in many matches and i want to take
>the 5 latest.
>
>The latest i want to multiply with 1.0 and then after with 0.8, 0.6, 0.4 and
>finally 0.2.
>
>playerid round score
>1, 10, 122
>1, 9, 123
>1, 8, 222
>1, 7, 333
>1, 6, 222
>
>And i want to multiply 122 * 1 + 123*0,8 + 222*0,6 + 333*0,4 + 222*0,2
>
>Even handle if there are only 3 score.
>
>Help, who can i do that in sql?
>
>Regards
>TW
>

Hi TW,

The following is untested. Please see www.aspfaq.com/5006 to find out
what you should post to enable me to test my suggestions:

SELECT s.playerid, SUM (f.factor * s.score)
FROM ScoreTable AS s
INNER JOIN (SELECT 1, 1.0 UNION ALL
SELECT 2, 0.8 UNION ALL
SELECT 3, 0.6 UNION ALL
SELECT 4, 0.4 UNION ALL
SELECT 5, 0.2) AS f(round, factor)
ON round = (SELECT COUNT(*)
FROM ScoreTable AS s2
WHERE s2.playerid = s.playerid
AND s2.round >= s.round)
GROUP BY s.playerid

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.

Relevant Pages

  • Query problem - top 5 validation
    ... I have a problem to run a query like this scenario: ... i have players in a table who have scored in many matches and i want to take ...
    (microsoft.public.access.queries)
  • query problem
    ... I have a problem to run a query like this scenario: ... i have players in a table who have scored in many matches and i want to take ...
    (microsoft.public.sqlserver.mseq)
  • Re: Checking if a set exists already -- non-normalized to normalized
    ... > Pierre Mullin submitted the following query to carry out the Todd ... Assume we have a table that tells us which players are on ... REFERENCES Players ... ON UPDATE CASCADE, ...
    (microsoft.public.sqlserver.programming)
  • The PlayersDB MatchPlay contest
    ... We're having a Harpoon III contest and you're invited! ... At the end of each game, send me the saved game file and I ... Players' results (losses to you and your enemy, Victory Conditions met, ... The opening scenario is a frantically paced air-to-air and ASuW scenario. ...
    (comp.sys.ibm.pc.games.naval)
  • Re: Lethality of events
    ... It really depends upon their motivations to play that game ... players were encouraged to approach the scenario with all the moral ...
    (rec.games.frp.advocacy)