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 |

You need a GROUP BY clause. (The current query is invalid and expected to raise an error.) — jarlh, Feb 23 '22 at 08:50
*"You need a GROUP BY clause. (The current query is invalid and expected to raise an error"* what jarth says is true this query is expected to give a error in the sql standards where this sql tag is about.. Are you using MySQL because MySQL allows this kind of invalid group by queries without sql_mode ONLY_FULL_GROUP_BY — Raymond Nijland, Feb 23 '22 at 09:01
Does this answer your question? [Get the row(s) which have the max value in groups using groupby](https://stackoverflow.com/questions/15705630/get-the-rows-which-have-the-max-value-in-groups-using-groupby) — pringi, Feb 23 '22 at 13:02

score 0 · Answer 1 · answered Feb 23 '22 at 08:55

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)

score 0 · Answer 2 · answered Feb 23 '22 at 08:58

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

SQL two table query

2 Answers2