An example (for a personal project) where I’m using a derived temp table (designated ‘x’) and a table joined against itself, used to find the max timestamp. Thanks, Martin!
select tt.*, fkid, max(fldTimeStamp) as `maxTimeStamp`, (count(*)-1) as `calcCount` from tbl_todo tt left join tbl_todo_stackable tts on tts.fkid = tt.id where fldEnabled = 1 and fldRecur in (0, 1) group by tts.fkid having date(`maxTimeStamp`) <> date(now()) union select tt2.*, fkid, max(fldTimeStamp) as `maxTimeStamp`, 0 as `calcCount` from tbl_todo tt2 left join tbl_todo_stackable tts2 on tts2.fkid = tt2.id where fldEnabled = 1 and fldRecur in (0, 1) group by tts2.fkid having max(fldTimeStamp) = fldCreateTimeStamp union select tt3.*, fkid, fldTimeStamp as `maxTimeStamp`, 0 as `calcCount` from tbl_todo tt3 left join tbl_todo_stackable tts3 on tts3.fkid = tt3.id where tts3.pkid_todo_stackable in ( select pkid_todo_stackable from ( select s1.pkid_todo_stackable, s1.fkid, s1.fldTimeStamp, s2.fldTimeStamp as date2 from tbl_todo_stackable s1 left join tbl_todo_stackable s2 on s1.fkid = s2.fkid and s1.fldTimeStamp < s2.fldTimeStamp where s1.fkid in (178, 179) -- hardcoded fkid's, but could use subquery having isnull(date2) -- using Martin's tbl join against itself to get max timestamp rows ) x ) and (datediff(now(), fldCreateTimeStamp)) % 7 = 0 and date(now()) <> date(fldTimeStamp) order by fldRecur desc, id;