Manoj Madushantha Manoj Madushantha - 1 month ago 10x
MySQL Question

SQL MAX() Function return wrong tuple/result

I make a Student assigment system in php/mysql. here table structure.

Student ------StudentAssignment -------Assignment -----attempt

StudentAssignment - stuID, assID, attID
attempts - attID, marks, assDate

student can do 3 attempts for one assignment.
I use following SQL statement to get MAX marks with its attDate.

SELECT a.title, MAX(att.marks), a.totalmarks, att.attDate
FROM attempts att, studentassignment sa, assignment a
WHERE sa.attID=att.attID AND sa.assID=a.ass_ID AND sa.assID=:assID AND sa.stuID=:stuID

but It return Max Mark with wrong attDate. attDate that return is first row's date of above sql statement.

Result set without using MAX Function

Using MAX function


You have numerous issues with your query:

  • Never use commas in the FROM clause. Always use explicit JOIN syntax.
  • Never mix aggregation functions and unaggregated columns unless the unaggregated columns are in a GROUP BY.

For your purposes, you can use ORDER BY and LIMIT, rather than MAX():

SELECT a.title, att.marks, a.totalmarks, att.attDate 
FROM attempts att JOIN
     studentassignment sa
     ON sa.attID=att.attID JOIN
     assignment a 
     ON sa.assID=a.ass_ID
WHERE sa.assID = :assID AND sa.stuID = :stuID 
ORDER BY att.marks DESC

Remember, MAX() is a function that returns only the maximum value for a column. It has no effect on the other values returned by the query.