The other day I was looking for a way to find only the most recent piece of data stored in a historical database of 40 sensors. Thanks for all the helpful suggestions. While no one suggested a fully working solution the suggestions got me pointed in the right direction. In case anyone might be lurking with a similar problem here is the gist of what I was needing to do and the solution I developed.
We have a system that collects data from 40 different sensors. Each reading is automatically stored along with the time and date of the measurement in the MySQL database. For monitoring purposes we wanted a web page on our Intranet that would show the most recent reading from each sensor, its name, and timestamp. The database currently holds about 20,000 records. The solution below uses a temporary table since sub-queries aren't available in the current version of MySQL that we use. A version of this query is actually a part of a PHP script on the internal webserver. I don't claim it's particularly elegant or even the best way to do it but I've been wrestling with this off and on for several days now and this seems to be working well. --- start sql file --- -- T. McDonough -- OFR, Inc. -- 08-March-2004 -- MySql Version 4.0.16 Win32 -- Finds the most recent sensor report for each unique -- sensor. -- Make a temporary table to hold name and date create temporary table tmp ( unitnum varchar(100), posdt datetime ); -- lock the main table in preparation for the queries... we don't want -- the data to change between the insert and the select. lock tables sensor read; -- extract the unit names with the most recent time and dates and load -- that data into the temporary table. insert into tmp select unitnum, max(posdt) as posdt from sensor where unitnum like 'OFR-%' group by unitnum; -- extract data from the main table only where it matches what's in the -- temporary table. select sensor.unitnum, sensor.posdt, sensor.value, tmp.unitnum, tmp.posdt from sensor, tmp where sensor.unitnum = tmp.unitnum and sensor.posdt = tmp.posdt; -- unlock the tables so so other processes can use them unlock tables; -- get rid of the temporary table. drop table tmp; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
