SELECT id_field WHERE max(date_field) < 'some date'

Question

I'm sure this question is obvious, but I've been fiddling with an increasingly complex set of subqueries for the last hour or so and getting nowhere.

I need to select a bunch of ID's from a table where the last date for that record is before a given date. I was trying:

SELECT id_field WHERE max(date_field) < 'some date'

But getting 'can't have aggregate in where field'. I've considered that I could SELECT where there are no dates above a certain date, but at this point my brain is waving it's little white flag.

Thanks.

Answer 1

SELECT id_field
     , max(date_field)
  FROM tbl
GROUP BY id_field
HAVING max(date_field) < 'some date'

Answer 2

Use HAVING instead of WHERE.

Answer 3

SELECT id_field 
  FROM tbl 
 GROUP BY id_field 
HAVING max(date_field) < 'some date'