Using 2 Aggregation in Having Clause

Question

If I have table that contains data for all the teams from many leagues, how I can get the first team from each league? The first team in each league should be the team with most point, or if points are equal the team with best goal difference (GF-GA)

For example:

League	Team	GF	GA	Points
Champion Group A	Bayren	18	5	16
Champion Group A	Atlteico Madrid	7	8	9
Champion Group H	PSG	13	6	12
Champion Group H	RB Leipzig	11	12	12

I want to get result like:

League	Team
Champion Group A	Bayren
Champion Group H	PSG

SELECT TEAM, LEAGUE
FROM LEAGUES_TABLE
GROUP BY LEAGUE
HAVING MAX(POINTS)  //or if point equal use MAX(GF-GA)

forpas · Accepted Answer · 2021-02-14T13:20:59.917

You can do it with ROW_NUMBER() window function:

SELECT league, team, gf, ga, points
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY league ORDER BY points DESC, gf - ga DESC) rn
  FROM leagues_table
)
WHERE rn = 1

Or with NOT EXISTS:

SELECT lt1.* FROM leagues_table lt1
WHERE NOT EXISTS (
  SELECT 1 FROM leagues_table lt2
  WHERE lt2.league = lt1.league 
    AND (lt2.points > lt1.points 
         OR (lt2.points = lt1.points AND lt2.gf - lt2.ga > lt1.gf - lt1.ga)
        ) 
)

Or with a correlated subquery:

SELECT lt1.* FROM leagues_table lt1
WHERE lt1.rowid = (
  SELECT lt2.rowid
  FROM leagues_table lt2
  WHERE lt2.league = lt1.league
  ORDER BY lt2.points DESC, lt2.gf - lt2.ga DESC
)

See the demo.
Results:

League	Team	GF	GA	Points
Champion Group A	Bayren	18	5	16
Champion Group H	PSG	13	6	12

Thanks. I'm not familiar with the PARTITION syntax. I used the NOT EXISTS solution and it works great — Liron C, Feb 14 '21 at 09:35

score -1 · Answer 2 · answered Feb 14 '21 at 06:23

-1

Try this. I didn't test this, but in my mind, this should work.

with team_points_ranked as (SELECT TEAM, LEAGUE, GA, GF
FROM LEAGUES_TABLE
GROUP BY LEAGUE
HAVING MAX(POINTS))

select TEAM, LEAGUE
from team_points_ranked
group by LEAGUE
having MAX(GF-GA)

answered Feb 14 '21 at 06:23

DDJ

5
4

1

It doesn't seems to be working. If I understand the syntax then it first creates view with the team with most point and then try to get the team with Max(GF-GA). Since the view already getting the MAX(points), then it won't return team with the same points under the same league. bdw, I didn't mark it as not useful, but I think someone mark it as it missing explanations. Thanks anyway. – Liron C Feb 14 '21 at 06:48
This is what happens when I don't test the code I suggest. lol. No problem. I was thinking if there are 2 teams with equal max points, then it would return both teams in "team_points_ranked". Are you getting a syntax error? And yes, I should have added explanation. Newbie here and learning it the hard way. – DDJ Feb 14 '21 at 06:57
It doesn't have syntax error, but will always get the same team with max point, regardless of the (GF-GA). – Liron C Feb 14 '21 at 07:08

Using 2 Aggregation in Having Clause

2 Answers2