MySQL need help.. how can I filter max(date)

Question

i have a table assumed like this

[table a]
UID | UNAME
------------------
001 | a
002 | b
003 | c

[table b]
UID | LOGS
----------------------
001 | 2009/08/01
001 | 2009/08/03
003 | 2009/08/02

and i want to have a query like this
UID | LASTLOG
--------------------
001 | 2009/08/03
002 | NULL
003 | 2009/08/02

that result i have already done it, but i faced a problem while i want to add filter lastlogin, assumed if i entered filter to show all users who has lastlogin before '2009/08/03', i want result like this

UID | LASTLOG
--------------------
003 | 2009/08/02

which eliminate UID 001 because 001 has lastlogin at 2009/08/03

i use sql command like this

SELECT a.uid, max(logs.date)
FROM user a LEFT JOIN logs ON (a.uid = logs.uid)
where max(logs.date)<'2009/08/03'
group by a.uid

how can i add a filter to show only max(logs.date) before 2009/08/03, because line 3 "where max(logs.date)<'2009/08/03' causes an error invalid group by function. thanks

Answer 1

Try this:

SELECT a.uid, max(logs.date)
FROM user a LEFT JOIN logs ON (a.uid = logs.uid)
group by a.uid
having max(logs.date)<'2009/08/03'

Answer 2

Any condition that uses a grouping function needs to go in the HAVING clause like so:

SELECT a.uid, max(logs.date)
FROM user a LEFT JOIN logs ON (a.uid = logs.uid)
GROUP BY a.uid
HAVING MAX(logs.date)<'2009/08/03'

Answer 3

You want to use the SQL "having" clause:

SELECT a.uid, max(logs.date)
FROM user a LEFT JOIN logs ON (a.uid = logs.uid)
group by a.uid
having max(logs.date) < '2009/08/03'

You need to use "having" instead of "where" to test the value of aggregate functions.