Sports Ranking SQL Problem

Question

Sports Ranking SQL Problem

mstephens

Mr or Mrs. 500

Points: 525
More actions
October 15, 2001 at 2:56 am

#77420

I have a database for sports results. I have a table that holds entry details (Name, Class, Number etc) and a table for results which can be considered as just linking a given result to a given entry.
Each competitor has more than one run and the best N runs count. I know I can use SQL's top N syntax to pick say the top 2 out of 3 results to come up with an overall score but my problem is that we also have a number of rounds and each round can have a different number of runs and runs that count.
How can I use T-SQL to generate complete results lists for all competitors showing their combined result which might be total of best 2 from 3 in round 1 but best 3 from 4 in round 2 ?
Can this be done with a straightforward query or do I need to get more elaborate and use triggers to keep track of competitors best results as they are entered?
Many thanks for any help you can give.
Matt Stephens

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jwiner SSCrazy Points: 2241 More actions · Answer 1

Not sure I fully understand what you're trying to achieve, but have you tried using 'WITH ROLLUP' or 'WITH CUBE' to do your totals/subtotals?

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

I am with Jon. Can you post some samples so we know what you mean? I am guessing that you will need separate queries of each "round" or set of "rounds" for which you want results.

Steve Jones

steve@dkranch.net

mstephens Mr or Mrs. 500 Points: 525 More actions · Answer 3

Sorry if my post was unclear. To clarify, lets assume I have a small competition with just 4 competitors A, B, C and D. The competition will have round 1 in which each competitor gets 3 attempts but only their best 2 attempts count toward the final score.

Round 1 results as follows: (sorry for the formatting but I couldn't get tables to look right!)

Competitor Attempt1 Attempt2 Attempt3 Score

A_________ 3_______ 2_______ 1_______ 5

B_________ 8_______ 9_______ 10______ 19

C_________ 1_______ 1_______ 1_______ 2

D_________ 4_______ 5_______ 6_______ 11

Round 2, or in this case the final will take the best 2 competitors from round 1 and this time each will have 4 attempts and the best 3 will count for the overall score.

Round 2 Results:

Competitor Attempt1 Attempt2 Attempt3 Attempt4 Score

B_________ 1_______ 2_______ 3_______ 4_______ 9

D_________ 1_______ 1_______ 1_______ 1_______ 3

Now, the real crux of my problem is to create a results list for the entire competition which would look like this:

Position Competitor Score

1_______ B_________ 9

2_______ D_________ 3

3_______ A_________ 5

4_______ C_________ 2

I currently have this working as an access database with lots of VBA code and my solution was to keep a table which indexed into each competitors best results. Whenever results are added/updated or deleted I re-evaluate the best results for that competitor based on the highest round they have reached so far. My plan is to move over to SQL Server (MSDE) and try to get the queries and stored procedures to be more efficient than the current version because for certain operations it’s very slow and due to deadlines I have just coded around my lack of knowledge of SQL with lots of repetitive simple ADO queries that run slowly.

Thanks for taking the time to read this. Any help/ideas will be much appreciated.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

What I think you will have to do is have separate queries for each round. You would then need to process these to find out which people have not progressed to the next round.

Off the top of my head, I don't have a great solution, but I will look at this a little later when I have some time and see what I can come up with.

I assume you have two main tables:

Table A

Name Class ...

A 1

B 1

C 1

...

Results

Name Round Attempt Score

A______1_______1________3

A______1_______2________2

A______1_______3________1

B______1______ 1________8

B______1______ 2________9

B______1______ 3________10

C______1______ 1_______ 1

C______1______ 2_______ 1

C______1______ 3_______ 1

D______1______ 1________4

D______1______ 1________5

D______1______ 1________6

B______2______ 1________1

B______2______ 2________2

B______2______ 3________3

B______2______ 4________4

B______2______ 1________1

B______2______ 2________1

B______2______ 3________1

B______2______ 4________1

Can you post some DDL for the tables or do you care?

Steve Jones

steve@dkranch.net

mstephens Mr or Mrs. 500 Points: 525 More actions · Answer 5

Thanks for your ideas Steve. The tables that you describe are about right and would certainly be enough to test some kind of solution. What I currently have is a little more complex but only because we organise a number of events through the year where most of the competitors are the same people. As a result I have a personal_details table which holds a competitors name, age, sex etc in one central place and a competitions table which holds the name, location and date of each competition that is organised. An entries table is used to link a given person with a given event and category and assign them a bib number for use throughout the competition. All results are linked to a particular entry. The following diagram illustrates this: