CODEWITHSUNDEEP
mysqlsql
J.brink
J.brink

Reputation: 31

SQL show first name of player with max(height) from each team

I'm learning SQL and have to find the highest player from each team (4 teams in total, 2 players per team). I've done this, but now I have to find the name of these players. However, it only shows the name of the first player from each team instead of the name of the highest, while still showing the correct height.

I'm working with two tables: 

players: player_id, last, first, team_id, number
player_data: player_id, height, weight

This is the SQL code that's currently not working:

SELECT p.team_id, p.first, MAX(pd.height)
FROM players p, player_data pd
WHERE pd.player_id = p.player_id
GROUP BY p.team_id

I hope you can help!

Upvotes: 2

Views: 770

Answers (3)

Brian Patterson
Brian Patterson

Reputation: 1625

boom

http://sqlfiddle.com/#!9/a4eee7/5

select p.teamid, p.playerid, p.firstname, p.lastname,
   pd.playerid, pd.height, pd.weight
from players p, player_data pd 
where p.playerid = pd.playerid
group by p.teamid
order by pd.height

Upvotes: 0

jarlh
jarlh

Reputation: 44795

Have a sub-query that returns each team's max height.

SELECT p.team_id, p.first, pd.height
FROM players p
JOIN player_data pd
    ON pd.player_id = p.player_id
WHERE (p.team_id, pd.height) IN (SELECT p.team_id, MAX(pd.height)
                                 FROM players p
                                 JOIN player_data pd
                                     ON pd.player_id = p.player_id
                                 GROUP BY p.team_id)

Upvotes: 1

praveen muppala
praveen muppala

Reputation: 177

You could try using row_number() over(partition by) instead of group by. The below query partitions the data by teamid order by height and the where condition would select the first row selected.

select * from (
Select p.team_id,p.first,row_number() over(partition by p.team_id order by pd.height desc)rn
from players p, player_data pd
WHERE pd.player_id = p.player_id)
where rn = 1

Upvotes: 0

Related Questions