Unanswered: MYSQL ORDER BY "difference between two values on two different rows"
I've got a fantasy football database that keeps scores from games over the years. I've bolded the relevant columns in these two tables.
TABLE 1 (schedule) gameID
week awayTeam homeTeam
TABLE 2 (scores) gameID teamID position
In short, I want to be able to sort by the SMALLEST margin of victory and HIGHEST margin of victory.
The total score for that team is listed as a "TS" in the position column with the number in the score column. This could, of course, be calculated by adding up all players from a gameID and teamID, but I use this as a checksum when entering scores. So for each TABLE 1 entry, there are two associated "TS" entries in TABLE 2, and I want to compare them to find the smallest and largest margins.