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)

Answer 1

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