List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 28 2006 5:25pm
Subject:Re: How to select the last entry per item
View as plain text  
Here are the queries which illustrate Shawn's point. Now to sleep.

-- wrong
DROP TABLE IF EXISTS o1;
CREATE TABLE o1
SELECT customerid,shipcity,MAX(shippeddate) AS latest
FROM orders
GROUP BY customerid;

-- right
DROP TABLE IF EXISTS o2;
CREATE TABLE o2
SELECT DISTINCT o1.customerid,o1.shipcity,o1.shippeddate AS latest
FROM orders o1
LEFT JOIN orders o2
  ON o1.customerid=o2.customerid AND o1.shippeddate<o2.shippeddate
WHERE o1.shippeddate IS NOT NULL AND o2.customerid IS NULL
ORDER BY customerid;

-- 3 of 89 rows differ
SELECT
  MIN(TableName) as TableName, customerid, shipcity, latest
FROM (
  SELECT 'o1' AS TableName,customerid,shipcity,latest FROM o1
  UNION ALL
  SELECT 'o2' as TableName,customerid,shipcity,latest FROM o2
) AS tmp
GROUP BY customerid, shipcity,latest
HAVING COUNT(*) = 1;

PB

-----

Peter Brawley wrote:
> LOL, three late nights in a row, lose that last post o' mine.
>
> 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