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
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.385 / Virus Database: 268.5.0/325 - Release Date: 4/26/2006
>
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
