List:General Discussion« Previous MessageNext Message »
From:Tim Lucia Date:April 27 2006 5:51pm
Subject:RE: How to select the last entry per item
View as plain text  
Invert the problem ;-)

Sort descending by the time_stamp field and limit the result to 1, i.e. 

SELECT * FROM table_xyz ORDER BY time_stamp DESC LIMIT 1 

Tim

-----Original Message-----
From: Brian J. Matt [mailto:brian.matt@stripped] 
Sent: Thursday, April 27, 2006 1:37 PM
To: mysql@stripped
Subject: How to select the last entry per item


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.


Thanks

-Brian

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