LOL, three late nights in a row, lose that last post o' mine. PB ----- Shawn Green wrote: --- Peter Brawley <[EMAIL PROTECTED]> wrote:BrianHi, 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 advanceifthis is an old question. We are designing a simple a tracking database with a table ofentriesshowing the current location of each item in the system. Somethingsimple like this. Table_xyz item_id | location | status | time_stamp As the items move new time stamped entries are added to thedatabase.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 toreplacethe entry for an item when a location update is made because weneedto keep the history. We plan on removing items after a suitabledelaywhen 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; PBThanks -BrianPeter, I am surprised at you ;-) You should know that the query you sent won't work like you said. Here's your query suggestion:SELECT item_id, location,MAX(timestamp) FROM table_xyz GROUP BY item_id;The item_id will be unique (thanks to the GROUP BY item_id) and the MAX(timestamp) will also be correct (again because of the GROUP BY) but the middle column, `location`, will not necessarily be the location code of the record with the MAX(timestamp) :-( Because that column is neither part of the GROUP BY clause or covered by an aggregate function, the engine should throw an error. However, MySQL tries to be nicer than that so it just picks a random value from all of the rows where the item_id's are the same. The only way to get to the groupwize maximum (in this case the record with the latest date from a group of records sharing the same ID) is by using one of the techniques listed here (temp table, subquery, concat hack): http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html Shawn Green Database Administrator __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com |
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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]