[Solved] Sorting League tables

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

[Solved] Sorting League tables

Post by Nocton »

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.

Regards

Nocton
Last edited by Hagar Delest on Fri Feb 26, 2016 10:21 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.12 on Windows 10
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Sorting League tables

Post by MTP »

It's fairly convoluted (queries calling queries that call other queries) but, at least with my test data, it seems to work for two-way ties. (I think additional syntax would be needed if a three-way tie occurred.) Hopefully something similar will work in your actual database.
Attachments
LeagueSort.odb
(6.67 KiB) Downloaded 388 times
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Sorting League tables

Post by Nocton »

Thank you MTP for working this out. You have put some time into it and I really appreciate it.
I have gone through your example. As you say it seems to work. But I have trouble in understanding your SQL for the TieWinners Query:

Code: Select all

SELECT "A"."ClubID" AS "ClubIDA", "B"."ClubID" AS "ClubIDB", COALESCE ( "MatchResults"."WinnerID", "B"."ClubID", "MatchResults"."WinnerID" ) AS "TieWinnerID" FROM "TotalMatchesWon" "A" INNER JOIN "TotalMatchesWon" "B" ON "A"."MatchesWon" = "B"."MatchesWon" AND "A"."ClubID" <> "B"."ClubID" LEFT JOIN "MatchResults" ON "A"."ClubID" = "MatchResults"."WinnerID" AND "B"."ClubID" = "MatchResults"."LoserID"
Could you explain for me how the COALESCE function works? I know how COALESCE works generally, but I cannot see what your logic is for the two "MatchResults"."WinnerID" in the function.

Regards

Nocton
OpenOffice 4.1.12 on Windows 10
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Sorting League tables

Post by MTP »

The LEFT JOIN with the "MatchResults" table only succeeds if "ClubIDA" is the winner - if "ClubIDB" is the winner, the JOIN will result in a NULL for "WinnerID".

The COALESCE tests for NULL as a proxy of figuring out which team won. In the case of NULL, that means "ClubIDB" was the winner. In the case of NOT NULL, that means "ClubIDA" was the winner (meaning "ClubIDA" and "WinnerID" are the same, so the last field of the COALESCE could be "A"."ClubID" and the resultset would be identical).
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: [Solved] Sorting League tables

Post by Nocton »

OK it's becoming clearer ... but why are there two "MatchResults"."WinnerID" in the COALESCE function? Surely they must have the same value?

Next week when I have a bit more time, I shall incorporate your code into my real life data for the past two years and report back.
OpenOffice 4.1.12 on Windows 10
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: [Solved] Sorting League tables

Post by MTP »

COALESCE functions do

Code: Select all

IF [FirstField] IS NULL
THEN return [SecondField]
ELSE return [ThirdField]
So the first instance of "WinnerID" is testing for NULL.

The second instance of "WinnerID" (the third field) returns its value, IF the test of the first field resolved as NOT NULL.


I originally tried to write it as

Code: Select all

CASEWHEN("MatchResults"."WinnerID" IS NULL, "B"."ClubID", "MatchResults"."WinnerID" ) AS "TieWinnerID"
... but it didn't work. I'm not sure if I'm missing the correct syntax or if CASEWHEN just doesn't work with NULL values.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] Sorting League tables

Post by chrisb »

hello Nocton,

i've had a go at this & come up with the following:-
i found it best to create two queries. the field names used throughout are as provided by yourself so fingers crossed it works with your data.
Query 1. select & calculate the required values from your tables.
my thoughts are that we have one glaring omission, each year will bring a new season. as this is not part of the remit it is not accounted for.
this is a series of sub-queries. each process is duplicated home tie/away tie.
copy this code, paste it into a query window, select 'Run SQL command directly' & execute.
if success is achieved then create a view from this code & name it as 'vLeagueTable'.

Code: Select all

select C."ClubID", C."ClubName",
coalesce(H_WINS.H_WIN, 0) + coalesce(A_WINS.A_WIN, 0) "TotalMatchWins",
coalesce(H_STATS.G_WON, 0) +  coalesce(A_STATS.G_WON, 0) "GameWins",
coalesce(H_STATS.HP_QTY, 0) + coalesce(A_STATS.AP_QTY, 0) "HoopPoints",
coalesce(H_STATS.G_PLAYED, 0) +  coalesce(A_STATS.G_PLAYED, 0) "GamesPlayed"

from
"Clubs" C

left join	--total home game points & total home game wins & total home games played
(
select
	M."HomeClubID",
	sum(G."HomePoints") HP_QTY,
	count(*) G_PLAYED,
	sum(case when G."HomePoints" > G."AwayPoints" then 1 end) G_WON
from
"Matches" M
join
"GameResults" G
on G."MatchID" = M."MatchID"

group by M."HomeClubID"
) H_STATS

on H_STATS."HomeClubID" = C."ClubID"

left join	--total away game points & total away game wins & total away games played
(
select
	M."AwayClubID",
	sum(G."AwayPoints") AP_QTY,
	count(*) G_PLAYED,
	sum(case when G."HomePoints" < G."AwayPoints" then 1 end) G_WON
from
"Matches" M
join
"GameResults" G
on G."MatchID" = M."MatchID"

group by M."AwayClubID"
) A_STATS

on A_STATS."AwayClubID" = C."ClubID"

left join	--total home match wins
(
select M."HomeClubID", sum(case when G.HP > G.AP then 1 end) H_WIN
from

(select "MatchID", "HomeClubID" from "Matches") M
join
(select "MatchID", sum("HomePoints") HP, sum("AwayPoints") AP from "GameResults" group by "MatchID") G
on G."MatchID" = M."MatchID"

group by "HomeClubID"

) H_WINS
on H_WINS."HomeClubID" = C."ClubID"

left join	--total away match wins
(
select M."AwayClubID", sum(case when G.HP < G.AP then 1 end) A_WIN
from

(select "MatchID", "AwayClubID" from "Matches") M
join
(select "MatchID", sum("AwayPoints") AP, sum("HomePoints") HP from "GameResults" group by "MatchID") G
on G."MatchID" = M."MatchID"

group by "AwayClubID"

) A_WINS
on A_WINS."AwayClubID" = C."ClubID"
Query 2. present the data according to remit.
if more than two clubs have won the same number of matches & the number of matches won = max("TotalMatchWins") then this query will fail because the embedded query can only produce a single value.
i think this is not a sensible way to choose a winner because the formula fails in the case of a three or more way tie.
'GamesPlayed' = the total number of games played. if you want matches played then just divide 'GamesPlayed' by the number of games in a match ('GamesPlayed'/12).

the sum of the away points is deducted from the sum of the home points when:-
the total number of matches won by a home club = the total number of matches won by an away club & the home club & away club are not the same club & the number of matches won = max("TotalMatchWins")
this produces either a positive or negative value which can be used to sort the data.
create the query from this code & execute.

Code: Select all

--works when 2 clubs win the same number of matches & the number of matches won = max(matches won)
--fails when more than 2 clubs win the same number of matches & the number of matches won = max(matches won)
select "ClubName", "TotalMatchWins", "GameWins", "HoopPoints", "GamesPlayed"--, "TieResult"
from
(
select Q.*,
	(
		select sum(G."HomePoints") - sum(G."AwayPoints")

		from "Matches" M
		join "GameResults" G
		on G."MatchID" = M."MatchID"

		where M."HomeClubID" = Q."ClubID"
		and
		M."AwayClubID" = (select "ClubID" from "vLeagueTable" where "ClubID" <> Q."ClubID" and "TotalMatchWins" = Q."TotalMatchWins" and "TotalMatchWins" = (select max("TotalMatchWins") from "vLeagueTable"))
	)
	"TieResult"

from "vLeagueTable" Q
)

order by "TotalMatchWins" desc, coalesce("TieResult", 0) desc, "GameWins" desc, "HoopPoints" desc
Last edited by chrisb on Tue Mar 01, 2016 12:27 am, edited 1 time in total.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: [Solved] Sorting League tables

Post by Nocton »

Thank you, chrisb, for this additional work. I had already started following MTP's solution. However, I have also looked at your solution. The first query works straight away, but the second query, although it works, did not produce a meaningful answer, i.e. a table showing who beat whom.

Going back to MTP's example solution, I have got completely bogged because, as you correctly say "we have one glaring omission, each year will bring a new season". In fact it is worse than that: I also have three leagues and also each club may have more than one team (A, B, etc.) So for my existing reports I first have to select using a date filter to get the correct season and then sort by league to make sure that matches in different leagues are not compared and then also use the club ID to look up the Club Name from the Clubs table and then combine with the team letter from the Matches table to give overall team name, e.g. Club 1 (A). You can see that I oversimplified the problem for the example data! The result is that I have not been able to get a workable solution, although I feel I am fairly close and may need to break the problem down into more stages/queries. At the very least the information that you have both provided has enabled me to see how to approach the problem.

Regards

Nocton
OpenOffice 4.1.12 on Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] Sorting League tables

Post by chrisb »

i have simply produced a league table & have not looked at the solution posted by MTP.

if the top two clubs have won an equal number of matches then their positions are based on the result of the match played between them ('TieResult' = 'HomePoints' - 'AwayPoints').
the code for the second query in my original post does contain an error (now corrected).
the order by clause should have read order by "TotalMatchWins" desc, coalesce("TieResult", 0) desc, "GameWins" desc, "HoopPoints" desc
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: [Solved] Sorting League tables

Post by Nocton »

I have now completely solved my problem. I started again using my original queries to calculate the match wins. These queries first select the season/date range using a date filter chosen by the user, then the Team is assembled as Club Name plus Team Letter. So now in the subsequent queries for finding the tie winner, I am using a text variable (Team) instead of an integer (ClubID). As a result I used a CASEWHEN statement instead of the COALESCE statement, because I ended up testing an empty string rather than a null integer. The League must be carried along so that I can sort by league first, before applying the other sorts.

Thank you again MTP and chrisb for your help, especially MTP with an actual example for me to follow.

Regards, Nocton
OpenOffice 4.1.12 on Windows 10
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: [Solved] Sorting League tables

Post by MTP »

That is interesting that CASEWHEN works for a text column when it doesn't for an integer column. I'm glad you were able to successfully create the setup you were looking for. :)
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
Post Reply