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