Re: How to select the last entry per item
Here are the queries which illustrate Shawn's point. Now to sleep. -- wrong DROP TABLE IF EXISTS o1; CREATE TABLE o1 SELECT customerid,shipcity,MAX(shippeddate) AS latest FROM orders GROUP BY customerid; -- right DROP TABLE IF EXISTS o2; CREATE TABLE o2 SELECT DISTINCT o1.customerid,o1.shipcity,o1.shippeddate AS latest FROM orders o1 LEFT JOIN orders o2 ON o1.customerid=o2.customerid AND o1.shippeddate WHERE o1.shippeddate IS NOT NULL AND o2.customerid IS NULL ORDER BY customerid; -- 3 of 89 rows differ SELECT MIN(TableName) as TableName, customerid, shipcity, latest FROM ( SELECT 'o1' AS TableName,customerid,shipcity,latest FROM o1 UNION ALL SELECT 'o2' as TableName,customerid,shipcity,latest FROM o2 ) AS tmp GROUP BY customerid, shipcity,latest HAVING COUNT(*) = 1; PB - Peter Brawley wrote: LOL, three late nights in a row, lose that last post o' mine. PB - Shawn Green wrote: --- Peter Brawley <[EMAIL PROTECTED]> wrote: 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 Peter, 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]
Re: How to select the last entry per item
LOL, three late nights in a row, lose that last post o' mine. PB - Shawn Green wrote: --- Peter Brawley <[EMAIL PROTECTED]> wrote: 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 Peter, 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]
Re: How to select the last entry per item
Shawn, Yep that's the theory, but where (i) the aggregate result is a column value, rather than a sum or average for example, and (ii) id is unique, I have not been able to get MySQL to give a wrong value with that approach, eg try the following with the northwind database (it ought to be doable in one query, but this machine's version of the MySQL server crashed on that): -- 'wrong' max, omitting nulls DROP TABLE IF EXISTS o1; CREATE TABLE o1 SELECT orderid,shipcity,MAX(shippeddate) AS latest FROM orders WHERE shippeddate IS NOT NULL GROUP BY orderid; -- correct max, again omitting nulls DROP TABLE IF EXISTS o2; CREATE TABLE o2 SELECT orderid, shipcity, (SELECT MAX(shippeddate) AS latest FROM orders o2 WHERE o2.orderid=o1.orderid) AS latest FROM orders o1 GROUP BY orderid HAVING latest IS NOT NULL; -- report o1 and o2 rows which do not match: SELECT MIN(TableName) as TableName, orderid, shipcity, latest FROM ( SELECT 'o1' AS TableName,orderid,shipcity,latest FROM o1 UNION ALL SELECT 'o2' as TableName,orderid,shipcity,latest FROM o2 ) AS tmp GROUP BY orderid, shipcity,latest HAVING COUNT(*) = 1; Empty set (0.01 sec) PB - Shawn Green wrote: --- Peter Brawley <[EMAIL PROTECTED]> wrote: 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 Peter, 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]
Re: How to select the last entry per item
--- Peter Brawley <[EMAIL PROTECTED]> wrote: > 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 > > > > > Peter, 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select the last entry per item
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 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]
Re: How to select the last entry per item
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]
RE: How to select the last entry per item
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:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 1:37 PM To: mysql@lists.mysql.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
