Inscrit le: 20 Mar 2018
Grade: Prestige 5
|Posté le: Dim 22 Avr - 09:17 (2018) Sujet du message: Sorting League tables
I manage a database which I created several years ago that handles the matches played among various croquet clubs. For each league all the clubs in that league play one match against the other clubs in the league. A match consists of 12 games and each game consists of up to 13 hoop points.
The final results in the league are sorted by:
No. of Matches won
No. of Games won
No. of Hoop Points won.
I had no problem in producing the league table reports sorted as above. However, twice in the last two years it has happened that two clubs have scored exactly the same no. of matches, in which case an additional rule has been added - in the match that the two clubs played, the club that won is placed ahead of the club that lost (regardless of how many games and hoop points they won). Is there a way of achieving this extra sort using SQL? - currently I manually adjust, but it would be nice if it were automatic.
The tables I have are that are relevant:
Clubs (Fields: ClubID, ClubName, plus other fields not relevant to this problem)
Matches (Fields: MatchID, HomeClubID, AwayClubID plus other fields not relevant to this problem)
GameResults (Fields: GameID,MatchID, GameNo, HomePoints, AwayPoints)
I manipulate the data using SQL queries to give the final table/view for the report, View_RepLeaguesFinal with fields:
ClubName, TotalMatchWins, GameWins, HoopPoints, GamesPlayed sorted in order of TotalMatchWins, GameWins, HoopPoints
So in summary, when two clubs have the same TotalMatchWins I want to check which club beat the other and re-sort putting the winning club of the pair first.
I hope I have explained this problem clearly enough. Maybe someone has seen this problem before? If I can't do it with a SQL query then I'll have to write a macro to do it - or continue to manually adjust.
Any help will be apprecited.
I didn't find the right solution from the Internet.
Video marketing agency