MySQL Mulitple Table Query

[edh]

I am having trouble getting the desired results from two tables. Here is the schema of students and their test scores.

students TABLE - Student information:

id (primary/key id)
fname (First name of student)
lname (Last name of student)

scores TABLE - Test Scores throughout the year:

id (from students table above)
date (a date the test was taken thru year)
score (A score for the test)

I want to show something like:

students.fname students.lname MAX(scores.score)

joe schmoe 97
mary doe 82
john doe 78

I am trying to show the max score for all tests a given student has taken. Example: any student can take 10 or more tests, but I only want to show the highest score attained for a particular student.

I am using the following SELECT statement:

SELECT students.*,MAX(scores.score),scores.id FROM students,scores GROUP BY students.id ORDER BY scores.score ASC

I am not getting the desired results. What am I doing wrong?

Ed

[jweissig]

hello,

Max only selects the max value. You need to sum()

try this..

SELECT students.fname, students.lname, sum(scores.score)
FROM students, scores
WHERE students.id = scores.id
GROUP BY scores.score
ORDER BY scores.score ASC

or

SELECT students.*,sum(scores.score),scores.id FROM students,scores
GROUP BY scores.score
ORDER BY scores.score ASC