Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version

PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

create table basketball( year integer,team_name text,player_name text,total_points integer);

insert into basketball values (1992,'Magic','Shaq',2000);
insert into basketball values (1992,'Magic','Steve Kerr',4000);
insert into basketball values (1996,'Lakers','Shaq',2300);
insert into basketball values (1996,'Lakers','Magic',1000);
insert into basketball values (2004,'Heat','Shaq',3000);

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

select year, team_name,high_score from (
  select *, 
  max(total_points) over (partition by year, team_name) high_score 
  from basketball) t
where t.total_points = t.high_score
and t.player_name='Shaq'

year
team_name
high_score

1996
Lakers
2300

2004
Heat
3000

SELECT 2

select a.year, a.team_name, a.total_points from 
basketball a,
(select year, team_name, max(total_points) high_score
  from basketball 
  group by year, team_name) b
where a.year = b.year
and a.team_name = b.team_name
and a.total_points = b.high_score
and a.player_name='Shaq'

year
team_name
total_points

2004
Heat
3000

1996
Lakers
2300

SELECT 2