Error with max and group by

[Joe Pearl]

Hi,

My sql is rusty but I'm trying to solve a problem and I'm getting a  
result that does not make sense.

The table is

mysql> select * from cust_full;
+-------+---------+----------+------------+
| name  | item_id | location | acq_date   |
+-------+---------+----------+------------+
| Jim   |       1 | OH       | 2007-03-15 |
| Mary  |       2 | PA       | 2007-01-15 |
| Sally |       1 | OH       | 2007-03-15 |
| John  |       0 |          | 0000-00-00 |
| Jim   |       3 | PA       | 2008-01-03 |
+-------+---------+----------+------------+

I want to get back only the most recent entry for each person and I  
don't care about the order.  I want the result to show Jim with the  
acq_date of "2008-01-03", Mary and Sally with the location and date  
for all of them.  However, when I run what I think should be the sql,  
I get:

mysql> select name, item_id, location, max(acq_date) from cust_full  
group by name;
+-------+---------+----------+---------------+
| name  | item_id | location | max(acq_date) |
+-------+---------+----------+---------------+
| Jim   |       1 | OH       | 2008-01-03    |
| John  |       0 |          | 0000-00-00    |
| Mary  |       2 | PA       | 2007-01-15    |
| Sally |       1 | OH       | 2007-03-15    |
+-------+---------+----------+---------------+


Why am I getting the wrong location for Jim?  It should be "PA".

This is on a Mac.

joe.
813.528.3859
My LinkedIn profile:  http://www.linkedin.com/in/joepearl

" We could learn a lot from crayons... Some are sharp, some are  
pretty and some are dull. Some have weird names, and all are  
different colors, but they all have to live in the same box. " - unknown