Sports Ranking SQL Problem

  • mstephens

    Old Hand

    Points: 365

    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

  • jwiner

    SSCrazy

    Points: 2241

    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: 716629

    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

    Old Hand

    Points: 365

    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: 716629

    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

    Old Hand

    Points: 365

    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:

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

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