SQL Problem with Top 3

[cojiro]

I have a table that lists Golf round scores like this:

Golfer Score
--------------
1 85
1 100
1 77
1 99
2 110
2 33

I need to create a query that results in the average of each golfer's lowest 3 scores. I'm at a loss with this. I was hoping someone had a simple method for accomplishing this. Any help would be greatly appreciated!

 

[bborissov]

Because I don;t know the version you use here some code that should work in all (Hmm, at least from SQL Server 2000) versions:

[COLOR=blue]DECLARE[/color] @Golfers [COLOR=blue]TABLE[/color] (Golfer [COLOR=blue]int[/color], Score [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Golfers [COLOR=blue]VALUES[/color] (1,85)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Golfers [COLOR=blue]VALUES[/color] (1,100)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Golfers [COLOR=blue]VALUES[/color] (1,77)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Golfers [COLOR=blue]VALUES[/color] (1,99)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Golfers [COLOR=blue]VALUES[/color] (2,110)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Golfers [COLOR=blue]VALUES[/color] (2,33)


[COLOR=blue]DECLARE[/color] @Temp [COLOR=blue]TABLE[/color] (Golfer [COLOR=blue]int[/color], Score [COLOR=blue]int[/color], Id [COLOR=blue]int[/color] [COLOR=blue]IDENTITY[/color](1,1))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp
[COLOR=blue]SELECT[/color] * 
       [COLOR=blue]FROM[/color] @Golfers
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] Golfer, Score

[COLOR=blue]SELECT[/color] [COLOR=blue]Temp[/color].Golfer, [COLOR=blue]Temp[/color].Score
[COLOR=blue]FROM[/color] @Temp [COLOR=blue]Temp[/color]
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] Golfer, [COLOR=#FF00FF]MIN[/color](id) [COLOR=blue]AS[/color] Id
                   [COLOR=blue]FROM[/color] @Temp
                   [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] Golfer) Tbl1
[COLOR=blue]ON[/color] [COLOR=blue]Temp[/color].Golfer = Tbl1.Golfer AND
   [COLOR=blue]Temp[/color].Id -  Tbl1.Id < 3
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] [COLOR=blue]Temp[/color].Golfer, [COLOR=blue]Temp[/color].Score [COLOR=#FF00FF]DESC[/color]

BTW Temp is a not a good name but I choose it because it is easier for me

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[cojiro]

My table above is actually a view of several tables. Is there a way I can add that IDENTITY column to my view so this code will work?

 

[cojiro]

This a different solution I've found elsewhere in case anyone is interested:

SELECT A.Golfer, avg(A.Score)
FROM TableName A 
WHERE
(Select count(*) From TableName B where B.Golfer = A.Golfer and B.Score<A.Score) <= 2 
GROUP BY a.Golfer 
ORDER BY AVG(A.score)

Thanks for your help Borislav!

 

[bborissov]

You could do:

DEFINE @Temp TABLE (put all view fields here, ID Identity(1,1))
INSERT INTO @Temp
SELECT * from YourView

SELECT Temp.*
FROM @Temp Temp
INNER JOIN (SELECT Golfer, MIN(id) AS Id
                   FROM @Temp
                   GROUP BY Golfer) Tbl1
ON Temp.Golfer = Tbl1.Golfer AND
   Temp.Id -  Tbl1.Id < 3
ORDER BY Temp.Golfer, Temp.Score DESC

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

Just be aware that that correlated subquery you posted, cojiro, could be VERY slow for huge datasets.

If you have SQL 2005 something using the new row number function could be the best way.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[Lamprey13]

Here is another way (thanks to bborissov for the set up code):

DECLARE @Golfers TABLE (Golfer int, Score int)
INSERT INTO @Golfers VALUES (1,85)
INSERT INTO @Golfers VALUES (1,100)
INSERT INTO @Golfers VALUES (1,77)
INSERT INTO @Golfers VALUES (1,99)
INSERT INTO @Golfers VALUES (2,110)
INSERT INTO @Golfers VALUES (2,33)


SELECT
	Golfer,
	AVG(Score)
FROM
	(
		SELECT 
			A.Golfer,
			A.Score
		FROM
			@Golfers A
		INNER JOIN
			@Golfers B
			ON A.Golfer = B.Golfer
		WHERE
			A.Score >= B.Score
		GROUP BY
			A.Golfer,
			A.Score
		HAVING
			COUNT(*) BETWEEN 1 AND 3
	) AS T
GROUP BY
	Golfer

 

[Lamprey13]

And for 2005 you can do:

SELECT
	Golfer,
	AVG(Score)
FROM
	(
		SELECT 
			A.Golfer,
			A.Score,
			ROW_NUMBER() OVER (PARTITION BY Golfer ORDER BY Score ASC) AS RowNum
		FROM
			@Golfers A
	) AS Temp
WHERE
	RowNum < 4
GROUP BY
	Golfer

 

[cojiro]

There's a lot great info on this thread. Thanks everyone!