List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 28 2006 5:10pm
Subject:Re: How to select the last entry per item
View as plain text  
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