SQL "best x out of y" summary Query 1

[dougmbti]

All,
I have a question. I have an application using SQL 2000 that stores tournaments, results, teams, etc. for a fishing league. It's comparable to a softball or golf league except with fishing tournaments instead.

Anyway, we are coming to the end of the year and I need to start summarizing the results for plaques, prizes, etc. and I need to use the "best x out of y" summary. The results need to be summarized by dropping the lowest scoring event.

I was curious if there was a query or combination of queries that could perform this for me so I don't have to make multiple runs through the data.

The results table is organized as follows:
resultNo, teamNo, tournamentNo, tournDate, result, etc.

Any help would be greatly appreciated.

TIA,
Doug

 

[gmmastros]

>> Here is a new spin ...

You must be a manager!

>> How would you drop the lowest 2,3,etc. tournaments? Would you just run the "delete" section multiple times?

Yes

>> Also, what if you wanted to also show the original number of events/points as well?

This query should return the number of events and sum of points for each team.

Select TeamNo, Count(1), Sum(Points)
From   @Result
Group By TeamNo

So, you can combine the 2 queries to return all the data, like so...

Select A.TeamNo, 
       Avg(A.Points) As AveragePoints,
       B.TournamentCount,
       B.TotalPoints
From   @Temp As A
       Inner Join (
          Select TeamNo, 
                 Count(1) As TournamentCount, 
                 Sum(Points) As TotalPoints
          From   @Result
          Group By TeamNo
          ) As B On A.TeamNo = B.TeamNo
Group By TeamNo

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dougmbti]

Being a manager - is it that obvious?

I'm assuming that the new query will replace the last query in the series ...

Thanks again for all your help!

Doug

 

[dougmbti]

Feel like I'm beating a dead horse here ...

Given this below of the query, what would have to change in order to only drop results after the 4th event given the best 4 out of 5 is the desired total?

Ex. Let's say a team fishes 1,2,3 or 4 of the 5 events, then they should not have any results droppped. If a team fishes all 5, they should only have 1 event dropped.

Same scenario for best 10 out of 12. No results should be dropped if they fish 10 or less.

Delete T
From @Temp T
Inner Join
(
Select Max(RowId) As RowId,
TeamNo
From @Temp
Group By TeamNo
Having Count(1) > 1
) As A On T.RowId = A.RowId

Thanks again for all your help
Doug