SQL two table query

Question

I have 2 tables:

Players

ID	Name
1	John
2	Maya
3	Carl

Results

ID	Player_ID	Result
1	1	250
2	1	300
3	2	100
4	2	350
5	3	500

I want to select all the names from the table Players and the top scores of each person. What I have so far:

SELECT Players.Name, max(Results.Result)
FROM Players JOIN Results
WHERE Players.ID = Results.Player_ID

But this only selects

| Carl | 500 |

and I want

| John | 300 |

| Maya | 350 |

| Carl | 500 |

Answer 1

try with a condition on the result : it needs to be the highest (max) for the player ID.

Try this:

SELECT p.Name, r.result FROM Players p JOIN Results r WHERE p.ID = r.Player_ID and r.result = (select max(result) from results rr where rr.Player_ID = p.ID)

Answer 2

You need to GROUP BY Players.ID, Players.Name to your query. I added Players.ID in case two players have the same name:

SELECT Players.Name, max(Results.Result)
FROM Players JOIN Results
WHERE Players.ID = Results.Player_ID
GROUP BY Players.ID, Players.Name