SQL JOIN return only the first match-CodePudding

I think this is a basic question, but I couldn’t figure it out. I am new to this so please bear with me.

I am analyzing players data from the FIFA game, and I want to get a table with the highest rating of each individual player and the earliest age when that happened.

This is an example of the data:

id	name	position	rating	age
1	James	RW	70	20
1	James	RW	71	21
2	Frank	CB	73	23
2	Frank	CB	73	24
3	Miles	CM	75	27
3	Miles	CM	74	28

This is what the query should return:

id	name	position	rating	age
1	James	RW	71	21
2	Frank	CB	73	23
3	Miles	CM	75	27

I thought I could first get the highest overall for each player, and then do a JOIN to get the age, but that gives the other years a player had the same highest rating.

id	name	position	rating	age
1	James	RW	71	21
2	Frank	CB	73	23
2	Frank	CB	73	24
3	Miles	CM	75	27

Thank you,

CodePudding user response：

Try this one (could be a bit faster):

with mytable as (
    select 1 as id, "James" as name, "RW" as position, 70 as rating,  20 as age union all 
    select 1, "James", "RW", 71, 21 union all
    select 2, "Frank", "CB", 73, 23 union all
    select 2, "Frank", "CB", 73, 24 union all
    select 3, "Miles", "CM", 75, 27 union all
    select 3, "Miles", "CM", 74, 28
)
select array_agg(t order by rating desc, age asc limit 1)[OFFSET(0)].*,
from mytable as t
group by t.id

CodePudding user response：

One approach uses ROW_NUMBER with QUALIFY:

SELECT *
FROM yourTable
WHERE true
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY rating DESC, age) = 1;

CodePudding user response：

Try this one too if you want. I think it's what you need.

select p1.id,
       p1.name,
       p1.pos,
       max(p1.rating),
       p1.age
  from players p1
  join (select id, min(age) min_age
          from players 
         group by 1) p2 on p1.id = p2.id and p1.age = p2.min_age
 group by p1.id, p1.name, p1.pos, p1.age