List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 28 2006 4:43pm
Subject:Re: How to select the last entry per item
View as plain text  
Shawn,

Yep that's the theory, but where (i) the aggregate result is a column 
value, rather than a sum or average for example, and (ii) id is unique, 
I have not been able to get MySQL to give a wrong <second_col> value 
with that approach, eg try the following with the northwind database (it 
ought to be doable in one query, but this machine's version of the MySQL 
server crashed on that):

-- 'wrong' max, omitting nulls
DROP TABLE IF EXISTS o1;
CREATE TABLE o1
SELECT orderid,shipcity,MAX(shippeddate) AS latest
FROM orders
WHERE shippeddate IS NOT NULL
GROUP BY orderid;

-- correct max, again omitting nulls
DROP TABLE IF EXISTS o2;
CREATE TABLE o2
SELECT
  orderid,
  shipcity,
  (SELECT MAX(shippeddate) AS latest FROM orders o2 WHERE 
o2.orderid=o1.orderid) AS latest
FROM orders o1
GROUP BY orderid
HAVING latest IS NOT NULL;

-- report o1 and o2 rows which do not match:
SELECT MIN(TableName) as TableName, orderid, shipcity, latest
FROM (
  SELECT 'o1' AS TableName,orderid,shipcity,latest FROM o1
  UNION ALL
  SELECT 'o2' as TableName,orderid,shipcity,latest FROM o2
) AS tmp
GROUP BY orderid, shipcity,latest
HAVING COUNT(*) = 1;
Empty set (0.01 sec)

PB

-----

Shawn Green wrote:
> --- Peter Brawley <peter.brawley@stripped> wrote:
>
>   
>> Brian
>>     
>>> Hi,
>>>
>>> I hope this is the right list for this question. If not, I'm happy
>>> to get help on where to post this question.  Apologies in advance
>>>       
>> if 
>>     
>>> this is an old question.
>>>
>>> We are designing a simple a tracking database with a table of
>>>       
>> entries 
>>     
>>> showing the current location of each item in the system.  Something
>>>       
>>> simple like this.
>>>
>>> Table_xyz
>>> item_id | location | status | time_stamp
>>>
>>> As the items move new time stamped entries are added to the
>>>       
>> database.  
>>     
>>> How would you query to find the current location of all the items 
>>> currently in the system.  As you might expect we don't want to
>>>       
>> replace 
>>     
>>> the entry for an item when a location update is made because we
>>>       
>> need 
>>     
>>> to keep the history.  We plan on removing items after a suitable
>>>       
>> delay 
>>     
>>> when they reach their destination.
>>>       
>> An item is in the most recent location for that item_id, right? Then
>> ...
>>
>> SELECT item_id, location,MAX(timestamp)
>> FROM table_xyz
>> GROUP BY item_id;
>>
>> PB
>>     
>>> Thanks
>>>
>>> -Brian
>>>
>>>
>>>       
> Peter, 
>
> I am surprised at you ;-)  You should know that the query you sent
> won't work like you said. Here's your query suggestion:
>
>   
>> SELECT item_id, location,MAX(timestamp)
>> FROM table_xyz
>> GROUP BY item_id;
>>     
>
> The item_id will be unique (thanks to the GROUP BY item_id) and the
> MAX(timestamp) will also be correct (again because of the GROUP BY) but
> the middle column, `location`, will not necessarily be the location
> code of the record with the MAX(timestamp) :-(
>
> Because that column is neither part of the GROUP BY clause or covered
> by an aggregate function, the engine should throw an error. However,
> MySQL tries to be nicer than that so it just picks a random value from
> all of the rows where the item_id's are the same.  The only way to get
> to the groupwize maximum (in this case the record with the latest date
> from a group of records sharing the same ID) is by using one of the
> techniques listed here (temp table, subquery, concat hack):
>
> http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
>
> Shawn Green
> Database Administrator
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
>
>
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.0/325 - Release Date: 4/26/2006
Thread
How to select the last entry per itemBrian J. Matt27 Apr
  • RE: How to select the last entry per itemTim Lucia27 Apr
  • Re: How to select the last entry per itemTerry Burton28 Apr
  • Re: How to select the last entry per itemPeter Brawley28 Apr
    • Re: How to select the last entry per itemShawn Green28 Apr
      • Re: How to select the last entry per itemPeter Brawley28 Apr
      • Re: How to select the last entry per itemPeter Brawley28 Apr
        • Re: How to select the last entry per itemPeter Brawley28 Apr