Why does this query not permit me from doing a group by?

Question

I want this to show me the most recent submitted record by ID, but I only want it for when the most recent is between these two dates. It is not, however, allowing me to group.

select id, type, location_id, max(submitted)
  from events 
 where type='status'
   and max(submitted) between '2014-08-05' and '2014-08-22'
 group by id

Answer 1

Use the having clause when using aggregate functions like max()

select id, type, location_id, max(submitted) 
from events 
where type='status'
group by id, type, location_id
having max(submitted) between '2014-08-05' and '2014-08-22'