On 4/27/06, Brian J. Matt <[EMAIL PROTECTED]> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
