[SOLVED] Filter on MAX in query

[compudocs]

 

I am trying to query info from two tables and filter the query on the MAX date on one of field within the query. I need to do this with a query because I need to export this query via function(exportcsv).

 

Here is what I have:

$query_Recordset1 = "SELECT Customer.beenawhile, Customer.CID, MAX(Ticket.ID) as ID, MAX(Ticket.CDate) as MCDate FROM Customer INNER JOIN Ticket ON Customer.CID = Ticket.CID WHERE Email IS NULL AND MCDate < $sixmonthsago Group By CID Order by Ticket.CDate";

 

I would like to be able to add  "AND MCDate < $sixmonthsago "  in the WHERE but it doesn't like that.  The MCDate is a MAX(Ticket.CDate) within the query(see query). I have shortened up the query for readablility.

 

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Group By CID Order by Ticket.CDate' at line 1

 

 

Does anyone know how to do this? Function, array???

 

Thanks for any help

Steve

[compudocs]

MySQL server version = 5.0

[Ken2k7]

SELECT c.beenawhile, c.CID, MAX(t.ID), MAX(t.CDate) AS MCDate
FROM Customer c
INNER JOIN Ticket t
     ON c.CID = t.CID
WHERE c.Email IS NULL
HAVING MCDate < $something
GROUP BY c.CID
ORDER BY t.CDate

[compudocs]

After a little tinkering,  got this to work.  Thanks You!!!!

 

SELECT c.beenawhile, c.CID, MAX(t.ID), MAX(t.CDate) AS MCDate

FROM Customer c

INNER JOIN Ticket t

    ON c.CID = t.CID

WHERE c.Email IS NULL

GROUP BY c.CID

HAVING MCDate < $something

ORDER BY t.CDate