Query problem - top 5 validation

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
TWHi
It is always better to post DDL and example data see
http://www.aspfaq.com/etiquett?e.asp?id=5006
If the relationship between rounds is constant then you can use something
like:
CREATE TABLE #scores ( playerid int, [round] int, score decimal(8,3))
INSERT INTO #scores ( playerid, [round], score )
SELECT 1, 10, 122
UNION ALL SELECT 1, 9, 123
UNION ALL SELECT 1, 8, 222
UNION ALL SELECT 1, 7, 333
UNION ALL SELECT 1, 6, 222
INSERT INTO #scores ( playerid, [round], score )
SELECT 2, 9, 111
UNION ALL SELECT 2, 8, 222
UNION ALL SELECT 2, 7, 333
SELECT p.playerid,SUM(p.score*(1-(0.2*p.rank)))
FROM ( SELECT playerid,score,
(SELECT COUNT(*) FROM #scores s where s.[round] > r.[round] AND s.playerid =
r.playerid) as Rank
from #scores r ) p
where p.rank < 6
GROUP BY p.playerid
Alternatively use a table to store the multiplying factor.
John
"tw" <tw@.tactics.se> wrote in message
news:uyI$86lTFHA.616@.TK2MSFTNGP12.phx.gbl...
> 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
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Here is my guess
CREATE TABLE Scorecards
(player_id INTEGER NOT NULL,
round_nbr INTEGER NOT NULL,
PRIMARY KEY (player_id, round_nbr),
score INTEGER NOT NULL);
INSERT INTO Scorecards VALUES (1, 10, 122);
INSERT INTO Scorecards VALUES (1, 9, 123);
INSERT INTO Scorecards VALUES (1, 8, 222);
INSERT INTO Scorecards VALUES (1, 7, 333);
INSERT INTO Scorecards VALUES (1, 6, 222);
INSERT INTO Scorecards VALUES (1, 5, 222);
SELECT W.player_id, SUM(W.weighted_score)
FROM (
SELECT S1.player_id,
(S1.score
* CASE WHEN round_nbr
= (SELECT MAX(round_nbr)
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 1.0
WHEN round_nbr
= (SELECT MAX(round_nbr)-1
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.8
WHEN round_nbr
= (SELECT MAX(round_nbr)-2
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.6
WHEN round_nbr
= (SELECT MAX(round_nbr)-3
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.4
WHEN round_nbr
= (SELECT MAX(round_nbr)-4
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.2 ELSE 0.0 END)
FROM Scorecards AS S1)
AS W(player_id, weighted_score)
GROUP BY W.player_id;
Put this in a VIEW so the system will update it for you. You can also
use the proprietary, non-portable TOP option if you do not care about
standards. It might be faster in SQL Server, but a good optimizer
would pull out the common expressions in the WHEN clauses and compute
each MAX() only once, probably getting it from an index on the
primarykey. TOP has to do a partition or sort under the covers.|||Hi John,
Im very thankful of your sql code, it works perfect.
Sorry about not post dll, it was new for me.
How should the sql string look if i have a table with multiplying
factor?
tw
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
You would need to add table (this one if temporary for demonstration)
CREATE TABLE #Multipliers ( Rank int, factor decimal(8,3) )
INSERT INTO #Multipliers ( Rank, factor )
SELECT 1, 1
UNION ALL SELECT 2, 0.8
UNION ALL SELECT 3, 0.6
And change the query to something like:
SELECT p.playerid,SUM(p.score*m.factor)
FROM ( SELECT playerid,score,
(SELECT COUNT(*) FROM #scores s where s.[round] > r.[round] AND s.playerid =
r.playerid) as Rank
from #scores r ) p
JOIN #Multipliers m ON p.rank = m.rank -- restricted by number of entries
in #Multipliers
GROUP BY p.playerid
You could also fit this into Joes solution.
John
"t w" <tw@.tactics.se> wrote in message
news:uVASgDnTFHA.3980@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> Im very thankful of your sql code, it works perfect.
> Sorry about not post dll, it was new for me.
> How should the sql string look if i have a table with multiplying
> factor?
> tw
> *** Sent via Developersdex http://www.examnotes.net ***