Mathias wrote:
sorry if i wasn't clear. i mean not select puchase_date, but max(purchase_date),
i.e. use having clause.
The join field is certainly customerId, or .... There is not sufficient info on
tables.
Hope that helps
:o)
Mathias
How would you do that with HAVING?
I believe the subquery version would work something like this
SELECT c.customerName,
p1.customerID, p1.purchaseID, p1.purchaseDate, p1.purchaseValue
FROM customer c
JOIN purchases p1 ON c.customerID = p1.customerID
WHERE p1.customerID = (SELECT MAX(p2.purchaseDate
FROM purchases p2
WHERE p1.customerID = p2.customerID);
As I said, that requires mysql 4.1+. With earlier versions, you use a
temporary table, as described in the manual page I referenced.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]