Query Type w/ Highest Value

[McArthurGDM]

Hi,



I'm not sure how to go about designing a query that would allow this particular query output. It's beyond my SQL level. Assume I have a table, assignments, as such:

ASSIGNMENTS
ID	Students	Scores
1	A	74
2	A	66
3	A	36
4	B	73
5	B	94
6	C	68
7	C	59
8	C	94
9	C	85
10	D	9

I want to query out the highest score for each particular student. That is, I want my output as such:

QUERY
ID	Students	Scores
1	A	74
5	B	94
8	C	94
10	D	9

Any help in being able to create a query output like this would be appreciated!

Thanks,

[orange]

Try

Select students, Max(scores)
from YourTable
Group By Students
Order By students

see w3schools sql Max

[McArthurGDM]

Thanks,

This is a good start and on the right track. Unfortunately, I would still need to reference the particular ID associated with each record that I pull out - and grouping causes an issue with this.

[orange]

Try

SELECT *
FROM assignments
WHERE scores IN (
		SELECT Max(scores) AS MaxScore
		FROM ASSIGNMENTS
		GROUP BY Students
		ORDER BY students
		)

[McArthurGDM]

Thank you,

I had to tweak it a little and had some syntax issues, but I got it to work. Thanks for the help.