SELECT WHERE and MAX()

Databases
1

Hooray for descriptive thread titles!

OK, I’m stuck with a query which I thought’d be an easy one, but I just can’t get my head around it.
I could easily solve the problem with PHP, but I want to do it, if possible, in a query, because doing this in PHP would be inefficient.

Imagine the following, very simplified table layout

id (int),
account_id (int),
date_created (datetime)

What I want to select is: the distinct account_id’s where the last inserted record for that account_id is more than 2 months old.

What I have now is this (very incorrect query):


SELECT account_id AS id, MAX(date_created) AS last FROM account_note WHERE date_created < '2011-12-24' GROUP BY account_id

Please help! :slight_smile:

2

Try

SELECT 
    account_id AS id
  , MAX(date_created) AS maxdate
FROM account_note 
GROUP BY account_id
HAVING MAX(date_created) < '2011-12-24'
3

Of course, totally forgot about HAVING().
I’m so glad it’s almost weekend :wink:

Thanks!

4