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: MMORPG
    ... With Warhammer Online, you can join a scenario, which pits you against ... another team of players and you fight various scenarios (capture the ... flag, king of the hill, straight deathmatch etc etc). ... ultimate character. ...
    (uk.games.video.misc)
  • 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)
Loading