CREATE TABLE #Scores (
player int NOT NULL,
game_date datetime NOT NULL,
score integer NOT NULL,
CONSTRAINT PK_Scores_Player_Game PRIMARY KEY ( player, game_date )
);
INSERT INTO #Scores VALUES(1,'20000101',50);
INSERT INTO #Scores VALUES(1,'20000102',51);
INSERT INTO #Scores VALUES(1,'20000103',52);
INSERT INTO #Scores VALUES(1,'20000104',53);
INSERT INTO #Scores VALUES(1,'20000105',54);
INSERT INTO #Scores VALUES(1,'20000106',55);
INSERT INTO #Scores VALUES(2,'20000101',56);
INSERT INTO #Scores VALUES(2,'20000102',57);
INSERT INTO #Scores VALUES(2,'20000103',58);
INSERT INTO #Scores VALUES(2,'20000104',59);
INSERT INTO #Scores VALUES(2,'20000105',60);
INSERT INTO #Scores VALUES(2,'20000106',61);
INSERT INTO #Scores VALUES(3,'20000111',71);
GO
SELECT * FROM #Scores
ORDER BY player , game_date DESC;
/*
player game_date score
------ ----------------------- -----
1 2000-01-06 00:00:00.000 55
1 2000-01-05 00:00:00.000 54
1 2000-01-04 00:00:00.000 53
1 2000-01-03 00:00:00.000 52
1 2000-01-02 00:00:00.000 51
1 2000-01-01 00:00:00.000 50
2 2000-01-06 00:00:00.000 61
2 2000-01-05 00:00:00.000 60
2 2000-01-04 00:00:00.000 59
2 2000-01-03 00:00:00.000 58
2 2000-01-02 00:00:00.000 57
2 2000-01-01 00:00:00.000 56
3 2000-01-11 00:00:00.000 71
*/
GO
/*
#1. ANSI SQL way using a correlated sub-query
*/
SELECT s1.Player , AVG( s1.Score ) AS Avg_Score
FROM #Scores AS s1
WHERE ( SELECT COUNT( * ) FROM #Scores AS s2
WHERE s1.Player = s2.Player And s1.game_date <= s2.game_date ) <= 3
GROUP BY s1.Player
ORDER BY s1.Player;
/*
#2. ANSI SQL way flattened as a JOIN. But may be slower than #1 & #3.
*/
SELECT s.Player , AVG( s.Score ) AS Avg_Score
FROM (
SELECT s1.Player , s1.Score
FROM #Scores AS s1 JOIN #Scores AS s2
ON s1.Player = s2.Player
WHERE s1.game_date <= s2.game_date
GROUP BY s1.Player , s1.Score
HAVING COUNT( * ) <= 3
) AS s ( Player , Score )
GROUP BY s.Player
ORDER BY s.Player;
/*
#3. T-SQL method using TOP operator available in SQL70/2000.
*/
SELECT s1.Player , AVG( s1.Score ) AS Avg_Score
FROM #Scores AS s1
WHERE s1.game_date IN ( SELECT TOP 3 s2.game_date FROM #Scores AS s2
WHERE s1.Player = s2.Player
ORDER BY s2.game_date DESC )
GROUP BY s1.Player
ORDER BY s1.Player;
/*
Player Avg_Score
----------- -----------
1 54
2 60
3 71
*/
GO
DROP TABLE #Scores;
GO
This page was last updated on May 01, 2006 04:28 PM.