Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-10-22 : 09:37:51
|
| Matt writes "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?For example, 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 ScoreA_________ 3_______ 2_______ 1_______ 5B_________ 8_______ 9_______ 10______ 19C_________ 1_______ 1_______ 1_______ 2D_________ 4_______ 5_______ 6_______ 11Round 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 ScoreB_________ 1_______ 2_______ 3_______ 4_______ 9D_________ 1_______ 1_______ 1_______ 1_______ 3Now, the real crux of my problem is to create a results list for the entire competition which would look like this:Position Competitor Score1_______ B_________ 92_______ D_________ 33_______ A_________ 54_______ C_________ 2I 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." |
|
|
|
|
|
Sports results ranking for multi-round events