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 |
CodePudding user response：
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)
CodePudding user response：
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