I've found myself with a problem beyond my novice understanding of mysql and was wondering if anyone knows the answer to this without too much hassle...

Say I've got three columns, A B C
where A is a timestamp and B is a category and C is a count.

I'm currently retrieving the values for C where the timerange in A matches (regardless of B). But in the same call, I'd like be able to retrieve the MAXimum for C but only where ie. column B="apples"

ie.

SELECT * FROM produce WHERE A>lastmonth AND A<lastweek AND (B="apples" AND MAX(C))

Obviously the last bit is wrong. MySQL doesn't work that way.

But how can I make it work? Thanks for any ideas.

ps. the description for the "database" sub-forum should have the keyword "mysql" in the description, I almost missed it entirely...