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

 

[hmckillop]

Can you give a more explicit example of what you mean, e.g. include a data set and then what exactly you wish to have as result set. I am not clear on what it is you want to do.
Thanks

"I'm living so far beyond my income that we may almost be said to be living apart

 

[dougmbti]

Sorry ...
Remember back to high school or grade school and your overall grade in a class was based on an average of your test scores. Say you took 5 test all term, but the teacher dropped your lowest score - so your grade was based on your 4 best. Same concept here.

I have a bunch of teams that enter tournaments and fish for points, prizes, etc. for an entire season. At the end of the season, I have to hand out trophies, plaques, etc. based on who did the best.

The season has a total of 5 tournaments and in each tournament, the team accumulates 50 points for 1st, 49 for 2nd, etc. so each team will accumlate points all season long. I need to base the overall results on each team's best 4 results.

How can that be done without making multiple runs through the database?

Sample data:
tournamentNo,teamNo,points,place, etc.
1,1,50,1
1,2,49,2
1,3,48,3
.
.
.
2,1,49,2
2,2,50,1
.
.
.

I would like to show the final rankings for the teams based on the best 4 out of 5 tournaments.

Does that help?

TIA,
Doug

 

[jbenson001]

You want to drop the lowest score based on what, the points or the place?

 

[dougmbti]

The points.

 

[Gooser]

--pseudocode

SELECT TOP 4 teamNo
  FROM tableName
  ORDER BY points DESC  --?

v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

 

[dougmbti]

Let's say that over the course of the season there are 100 records in the results table. In those records are entries for the same team in different tournaments, etc.

Won't that code will only list the top 4 records period, not top 4 results per team?

 

[SQLDenis]

example

create table #test (team char(2), points int)
insert into #test values('T1',20)
insert into #test values('T1',30)
insert into #test values('T1',40)
insert into #test values('T1',50)
insert into #test values('T1',60)
insert into #test values('T1',70)
insert into #test values('T2',20)
insert into #test values('T2',30)
insert into #test values('T2',40)
insert into #test values('T2',50)
insert into #test values('T2',60)

select * from #test t
where not exists (select * from #test where t.team = team 
group by team
having t.points =min(points))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

In your example, Team 3 only participated in 1 tournament, so the following code accomodates this by only removing the worst score when there are more than 1 tournaments participating in.

The @Result part is for testing purposes. In your final query, you'll want to replace @Result with your actual table.

[green]-- setting up some test data[/green]
Declare @Result Table(TournamentNo Int, TeamNo Int, Points int, Place Int)

Insert Into @Result Values(1,1,50,1)
Insert Into @Result Values(1,2,49,2)
Insert Into @Result Values(1,3,48,3)
Insert Into @Result Values(2,1,49,2)
Insert Into @Result Values(2,2,50,1)

[green]-- the query starts here[/green]
Declare @Temp Table(RowId Integer Identity(1,1), TournamentNo int, TeamNo int, Points int)

Insert Into @Temp(TournamentNo, TeamNo, Points)
Select TournamentNo, TeamNo, Points
From   @Result
Order By TeamNo, Points

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

Select TeamNo, Avg(Points) As AveragePoints 
From   @Temp
Group By TeamNo

-George

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

 

[gmmastros]

Denis, your code fails when the last 2 points ar the same for a given team. Try adding....

insert into #test Values('T3',50)
insert into #test Values('T3',50)

One of the 50's should be dropped, but the other 50 should be kept.

-George

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

 

[Gooser]

SELECT team, points
   FROM #test
   GROUP BY team, points
   ORDER BY team, points DESC

--still working on this.

v/r

Gooser

 

[SQLDenis]

george you can eliminate some of the stuff
thiss will do the same

Declare @Result Table(TournamentNo Int, TeamNo Int, Points int, Place Int)

Insert Into @Result Values(1,1,50,1)
Insert Into @Result Values(1,2,49,2)
Insert Into @Result Values(1,3,48,3)
Insert Into @Result Values(2,1,49,2)
Insert Into @Result Values(2,2,50,1)

select TeamNo, Avg(Points) As AveragePoints  from @Result t
where not exists (select * from @Result where t.TeamNo = TeamNo 
group by TeamNo
having t.points =max(points)
and (count(*) >1))
group by TeamNo

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Denis,

Try adding....

Insert Into @Result Values(2,4,50,1)
Insert Into @Result Values(3,4,50,1)

Your results completely remove team 4 because both scores (of 50) are removed by your query.



-George

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

 

[SQLDenis]

No problem we will fix it right away, i promise same day service ;-)

small modification

Declare @Result Table(TournamentNo Int, TeamNo Int, Points int, Place Int)

Insert Into @Result Values(1,1,50,1)
Insert Into @Result Values(1,1,50,1)
Insert Into @Result Values(1,2,49,2)
Insert Into @Result Values(1,3,48,3)
Insert Into @Result Values(2,1,49,2)
Insert Into @Result Values(2,2,50,1)
Insert Into @Result Values(2,4,50,1)
Insert Into @Result Values(3,4,50,1)


select TeamNo, Avg(Points) As AveragePoints  from @Result t
where not exists (select * from @Result where t.TeamNo = TeamNo 
group by TeamNo
having t.points =max(points)
and (count(distinct points) >1))
group by TeamNo

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Denis,

Nice try. Because of this, I found a flaw in my proposed solution.

Earlier, I had you add...

Insert Into @Result Values(2,4,50,1)
Insert Into @Result Values(3,4,50,1)

If you also add...

Insert Into @Result Values(4,4,60,1)

Then... team 4 should have an average of 55. The 3 scores are 50, 50, and 60. One 50 should be removed, so you average 50 and 60 to get 55.

In my code, I add data to a table variable. While adding the data, I should have put it in to the table order by Points DESC. I inadvertently left out the DESC part, so, the query becomes...

[green]-- Test data[/green]
Declare @Result Table(TournamentNo Int, TeamNo Int, Points int, Place Int)

Insert Into @Result Values(1,1,50,1)
Insert Into @Result Values(1,2,49,2)
Insert Into @Result Values(1,3,48,3)
Insert Into @Result Values(2,1,49,2)
Insert Into @Result Values(2,2,50,1)
Insert Into @Result Values(2,4,50,1)
Insert Into @Result Values(3,4,50,1)
Insert Into @Result Values(4,4,60,1)

[green]-- the query starts here[/green]
Declare @Temp Table(RowId Integer Identity(1,1), TournamentNo int, TeamNo int, Points int)

Insert Into @Temp(TournamentNo, TeamNo, Points)
Select TournamentNo, TeamNo, Points
From   @Result
Order By TeamNo, Points [!]desc[/!]

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

Select TeamNo, Avg(Points) As AveragePoints 
From   @Temp
Group By TeamNo

This code produces the correct average for team 4.

Denis, I suspect you should be using Min, not max.

-George

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

 

[SQLDenis]

what a disaster, at least it's the same output

Declare @Result Table(TournamentNo Int, TeamNo Int, Points int, Place Int)

Insert Into @Result Values(1,1,50,1)
Insert Into @Result Values(1,2,49,2)
Insert Into @Result Values(1,3,48,3)
Insert Into @Result Values(2,1,49,2)
Insert Into @Result Values(2,2,50,1)
Insert Into @Result Values(2,4,50,1)
Insert Into @Result Values(3,4,50,1)
Insert Into @Result Values(4,4,60,1)

-- the query starts here
Declare @Temp Table(RowId Integer Identity(1,1), TournamentNo int, TeamNo int, Points int)

Insert Into @Temp(TournamentNo, TeamNo, Points)
Select TournamentNo, TeamNo, Points
From   @Result
Order By TeamNo, Points desc

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

Select TeamNo, Avg(Points) As AveragePoints 
From   @Temp
Group By TeamNo



select t.TeamNo, case tc when 1 then sum(Points)/tc else   (sum(Points)- msp)/(tc-1) end As AveragePoints 
from @Result t
join (select count(*) as tc,sum(points) as sp,TeamNo from @Result tt
group by TeamNo
) x on t.TeamNo =x.TeamNo
join (select min(points) as msp,TeamNo from @Result tt
group by TeamNo
) xx on t.TeamNo =xx.TeamNo
group by t.TeamNo,tc,msp

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dougmbti]

Holy Cow - you guys are awesome!!! I leave for lunch and you have all this for me!!!!

Do these queries take into account the fact that a team may not fish all tournaments and subsequently not have any results records for a given tournament?

Doug

 

[gmmastros]

My query supports it, but let me explain so that there is no confusion.

If there are 10 tournaments, and 1 team only participates in 1 tournament, their average will be the same as that 1 single score. If a team participates in 2 of the 10 tournaments, their average will be the same as their best score.

You could argue that this isn't fair for the teams that participated in every tournament, but the rules are yours, and are implemented as such.

My wife participates in a golf league. The rules for the league state that you can miss 1 game because the overall score is based on the top 11 of the 12 games. If you miss more than 1 game, you do not qualify for prizes at the end of the league. Of course, it's your tournament and your rules. This is just a suggestion.



-George

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

 

[dougmbti]

I agree with your suggestion, but in our case most teams fish all the tournaments. We drop one just incase they have a bad night or can't fish one night for whatever reason.

I've modified your final select and changed the avg(totalPoints) to become sum(totalPoints)/currentNum ....

The currentNum represents the current number of events that have been completed. So, if only 3 events have been fished, the currentNum = 3, etc.

That "evens out" the average so it fits better for those who fish the entire season ...

Again, Thanks for you help!!

Doug

 

[dougmbti]

Here is a new spin ...

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

Also, what if you wanted to also show the original number of events/points as well? As it stands now, just the information pertaining the the best x of y results are shown. How can I have my cake and eat it to so to speak?
ex:
Team,Number of Events,Total Points,Best 4 points, etc.
------------------------------------------------------
team1,5,300,275,etc.
team2,4,225,200,etc.
.
.
.

The reason I need Total number of events/points is for the crowning of the overall champion. That champion must have fished all events.

TIA,
Doug