Retrieving a the latest record sorted by initial character and date

[hgrimberg01]

I am trying to retrieve the latest customer order since 2011-01-01 for customers with first names beginning with a or A. I have this thus far. I have a feeling I'm very close to the answer but I'm stuck. At this point it returns all orders that meet the above criteria but I only want the latest order for each customer. I tried setting a limit and it works but it works only for one customer(that happens to have the latest order of the query).

 

Any suggestions/tips/ideas/comments?

 

Thank You.

 

Version 5.5.8

 

SELECT Customer.CustomerFirstName AS FirstName, Customer.CustomerLastName, Transaction.TransactionCustomerID, Transaction.TransactionDate AS TransactionDate
FROM Customer, Transaction
WHERE Customer.CustomerID = Transaction.TransactionCustomerID
AND DATE( Transaction.TransactionDate ) > '2011-01-01'
HAVING FirstName LIKE 'a%'
ORDER BY TransactionDate DESC

 

With the following output(sorry for the poor formatting):

FirstName CustomerLastName TransactionCustomerID TransactionDate

asadasdasd dasdasdasd 4 2011-02-27 14:37:01

asadasdasd dasdasdasd 4 2011-02-25 14:36:54

ADAC Dsdasdasd 7 2011-02-25 14:24:00

ADAC Dsdasdasd 7 2011-02-17 14:21:13

Output of EXPLAIN():

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE Transaction ALL NULL NULL NULL NULL 7 Using where; Using filesort

1 SIMPLE Customer eq_ref PRIMARY PRIMARY 4 test0.Transaction.TransactionCustomerID 1

 

 

[fenway]

Other than a missing index on first name, the correct way is to get a unique list of customers first, and then find the latest one for each.