Re: Error with max and group by

[Brent Baisley]

Your query is just grabbing the max date within the group, but you are  
not specifying which record you should be pulling for the location, so  
it's using an arbitrary one within the group. In your query, there is  
not relation between max date and location.

What you need to do is find out what the max date is, and then find  
out what record that max date belongs to so you can pull the rest of  
the information from that record. You can do this by use a select on a  
"virtual" table.

First, get the max date:
select name,max(acq_date) AS mx_acq_date from cust_full group by name;

Now you want to get the record associate with the matching name/max  
date, so you need to join the result of the above query with the same  
table:
select name, item_id, location, mx_acq_date from cust_full
join
(select name,max(acq_date) AS mx_acq_date from cust_full group by  
name) AS mx_cust_full
on cust_full.name=mx_cust_full.name AND  
cust_full.acq_date=mx_cust_full.mx_acq_date

The name+acq_date is going to be your unique string to join on. Your  
finding out the max, then finding out which record is associated with  
the max.

Brent Baisley
I write code.

On Jun 20, 2008, at 10:50 PM, Joe Pearl wrote:

> 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
>
>
>
>
>
>
>