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
