MySQL Forums
Forum List  »  Newbie

max(date) in where clause?
Posted by: Kate Burdon
Date: March 12, 2007 09:36AM

i have a database in which i am trying to query the number of accounts with a max(date changed flag) of 45+ days.

this query works but doesn't give me the result i'm looking for:

select count(nc.orderid) as cnt
from new_clients nc, history_log hl
where nc.orderid = hl.orderid
and nc.contentstatus = 94
and hl.changeid = 94
and changedate <= now() - interval 45 day

what i really want to do is:

select count(nc.orderid) as cnt
from new_clients nc, history_log hl
where nc.orderid = hl.orderid
and nc.contentstatus = 94
and hl.changeid = 94
and max(changedate) <= now() - interval 45 day

because there can be more than one changedate per orderid in the history_log table, and i only want the newest one. but it gives me

Error Code : 1111
Invalid use of group function

even if i add a group by clause to the query i still get this error. is this an impossible query?

Options: ReplyQuote


Subject
Written By
Posted
max(date) in where clause?
March 12, 2007 09:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.