List:General Discussion« Previous MessageNext Message »
From:Terry Burton Date:April 27 2006 10:53pm
Subject:Re: How to select the last entry per item
View as plain text  
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
Thread
How to select the last entry per itemBrian J. Matt27 Apr
  • RE: How to select the last entry per itemTim Lucia27 Apr
  • Re: How to select the last entry per itemTerry Burton28 Apr
  • Re: How to select the last entry per itemPeter Brawley28 Apr
    • Re: How to select the last entry per itemShawn Green28 Apr
      • Re: How to select the last entry per itemPeter Brawley28 Apr
      • Re: How to select the last entry per itemPeter Brawley28 Apr
        • Re: How to select the last entry per itemPeter Brawley28 Apr