I introduced the challenge of Scoring a Cross Country match in a 2005 article.
This article provides some SQL Solutions to parts of the Challenge.
1) Matching Teams:
In a four-way match each combination of teams, each team is scored against the others:
Teams: Ephrata, Manheim Township, Conestoga Valley, McCaskey
There are 6 pairs: 3+2+1
- Ephrata vs Manheim Township
- Ephrata vs Conestoga Valley
- Ephrata vs McCaskey
- Manheim Township vs Conestoga Valley
- Manheim Township vs McCaskey
- Conestoga Valley vs McCaskey
2) How do we generate these team pairings with SQL?
/* Get Pairs of Teams */ select t1.team_name as t1, t2.team_name as t2 from teams as t1 cross join /* Cross join two teams: generates all possible combinations */ teams as t2 where /* 'greater than' fix to make sure that teams don't race against themselves or their inverse */ t1.team_name > t2.team_name order by t1.team_name, t2.team_name;
3) How to we generate each runner’s points?
select rank() over (order by place) as points, team_pairs.team1, team_pairs.team2, race_results.runner_name, teams.team_name, race_results.place, race_results.time from ..
4) Combine Together Using “With-Statement”
/* Add the Race Results to the Team Parings */ with team_pairs as ( /* Team Pairs */ select t1.id as id1, t1.team_name as team1, t2.id as id2, t2.team_name as team2 from teams as t1 cross join teams as t2 where t1.team_name > t2.team_name /* could also use teams.id but I didn't want you to think the ids are meaningful */ order by t1.team_name, t2.team_name ) select rank() over (order by place) as points, team_pairs.team1, team_pairs.team2, race_results.runner_name, teams.team_name, race_results.place, race_results.time from team_pairs /* team_pairs is the 'named' variable from the above with statement */ left outer join race_results /* this could change to an'inner join' but I want to see all teams, evem if no runners */ on team_pairs.id1 = race_results.team_id or team_pairs.id2 = race_results.team_id inner join teams on teams.id = race_results.team_id /* There's a lot of data returned. Let's focus in on one match: Ephrata vs Manheim Township for which we at least have 4 runners. We can remove the filter once we have a single match worked out */ where ( team_pairs.team1 = 'Ephrata' and team_pairs.team2 = 'Manheim Township' ) or ( team_pairs.team2 = 'Ephrata' and team_pairs.team1 = 'Manheim Township' ) order by race_results.place; /* We can add another with statement and sum up the points for each team, grouping by team1, and team2 We also have to figure out how we're going to solve the 6th and 7th runner rule. I think we can do that with a where clause that limits the top 7 runners, which may require a second rank column and an order by statement, but I'm fuzzy on what I did before */