An item is in the most recent location for that item_id, right? Then ...
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.
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.
SELECT item_id, location,MAX(timestamp)
GROUP BY item_id;
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