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
