Postgres 9.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 9.3.25 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit

SELECT 1

create table team (id int, name text);
insert into team values (1,'red'),(2,'blue');

create table player (team_id int, name text, score int);
insert into player values (1,'ann',10),(1,'bob',7),(2,'cat',9),(2,'dan',3);

CREATE TABLE

INSERT 0 2

CREATE TABLE

INSERT 0 4

-- LATERAL arrived in PostgreSQL 9.3: the subquery can see t.id
select t.name, top.name as top_scorer, top.score
from team t
cross join lateral (
  select name, score from player p
  where p.team_id = t.id
  order by score desc limit 1
) as top
order by t.id;

name
top_scorer
score

red
ann
10

blue
cat
9

SELECT 2