Retrieving the latest record

[p8ass8]

There are students of different departments and classes that take different exams repeatedly based on certain requisites/rules. I need to pull the latest grades based on examdate for students of classid and departmentid that are passed to the stored procedure during execution.

Here is my query:

SELECT @MathGrade=MathGrade, @EngGrade=EngGrade
FROM MathHistory MH,StudentRecordSR, ExamHistoryMinimumRule EHMR
where MH.ClassID=SR.ClassID AND SR.DepartmentID=@DepartmentID
AND SR.StudentID=@StudentID
AND EHMR.MinimumRuleRESULT=1
AND MH.EXAMTYPEID IN (2)
AND EHMR.MinimumRuleID IN (1,2,6)
AND MH.ExamDate=(SELECT MAX(ExamDate) FROM MathHistory)

This query gives me the right results until I add the last AND condition to get the gradde for most recent exam date.

Can you help me?

 

[unclejimbob]

From what I can see this is because
"SELECT MAX(ExamDate) FROM MathHistory"
gives you the max exam date for mathhistory rather than just the max exam date for max history where maxhistory.classid = StudentRecord.classid, therefore you will need to include the same selection criteria for the subquery as you do for the main query

 

[p8ass8]

Thank you for your reply.

I think that might be it. I am about to try it right now, and will let you know if it works.