Using 2 Aggregation in Having Clause

user_4875293 0
Viewed 38

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)
having-clause sqlite
2 Answers

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
user_104988280

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)
user_114176800
Related