Using MAX function in MySQL query

Question

I have a SQL query where I would like to select rows from lesson table where idLesson column is greater than MAX from idLesson of attendance table. How can I correct this query?

SELECT student.userid, lessons.idLesson, student.fee, lessons.datePassed 
    FROM student
    Inner JOIN `project_course`.group ON group.idGroup = student.idGroup
    Inner JOIN lessons ON group.idGroup=lessons.idGroup 
    Inner JOIN attendance ON lessons.idLesson>MAX(attendance.idLesson)
    group by lessons.idlesson;

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Nov 29 '15 at 13:28

score 1 · Accepted Answer · answered Nov 29 '15 at 13:49

This is your query modified according to your explanation

where idLesson column is greater than MAX from idLesson of attendance table

SELECT student.userid, lessons.idLesson, student.fee, lessons.datePassed 
    FROM student
    Inner JOIN `project_course`.group ON group.idGroup = student.idGroup
    Inner JOIN lessons ON group.idGroup=lessons.idGroup 
WHERE lessons.idLesson > 
  ( SELECT MAX(attendance.idLesson)
    FROM attendance
  )

Perfect! That's what I need – M. Namiz Nov 29 '15 at 19:44 — M. Namiz, Nov 29 '15 at 19:44

Using MAX function in MySQL query

1 Answers1