List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 27 2006 6:31pm
Subject:Re: How to select the last entry per item
View as plain text  
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
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.0/325 - Release Date: 4/26/2006
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