I have a table names 'scoredata' having fields id, lessonid, levelid, studentid, addedate in which score for each day will be entered by the admin. I want to select only those studentid whose score has not been added from last four weeks. How can I do this in a single query.
SELECT studentid FROM ( SELECT MAX(addedate) lastdate,studentid FROM scoredata GROUP BY studentid ) A WHERE lastdate < ( DATE(NOW()) - INTERVAL 4 WEEK );
- The subquery gets the last date each student entered a score
- The WHERE clause outside the subquery checks to all lastdate values older than 4 weeks
Make sure you index
studentid,addedate. If you didn't then run
ALTER TABLE scoredata ADD INDEX studentid_addedate_ndx (studentid,addedate);