On 4/27/06, Brian J. Matt <brian.matt@stripped> wrote:
> 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.
If you are looking to obtain a result set the represents the current
location of all items in the system you can use a sub-select as
follows:
SELECT item_id AS lid,location,status,timestamp
FROM xyz
WHERE timestamp=(
SELECT MAX(timestamp) FROM xyz WHERE item_id=lid
)
For the sake of efficiency make sure you have a key on timestamp.
Hope this helps,
Tez
