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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
