Manoj Madushantha Manoj Madushantha - 3 months ago 15
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

Answer

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
LIMIT 1;

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.