I have a table which has the following columns (datacentre,machine,uptime,last_update), into which a row is inserted every 5 minutes for each machine in each datacentre, and the last_update column is the database system timestamp value. I would like to display the latest row for each machine using one SQL statement on DB2, but am having no luck so far!
This displays the last row for each machine, but omits the 'uptime' column. How would I also get it to also display the 'uptime' column for every row? I could easily do it with a couple of queries, but would prefer to get it all done in one for speed reasons.
yes, last_update is the time when the row was inserted into the database (every five minutes for each machine), whereas uptime is the time the machine has been up for. If the machine is rebooted the uptime could go from several months to a few seconds. This is why I want to query for the latest uptime for each machine.
with t1 (datacenter,machine,last_update) as (Select datacenter,machine,max(last_update) from mytable group by datacenter,machine) select t1.datacentr,t1.machine,mt.uptime,t1.last_update from t1 inner join mytable as mt on (t1.datacenter = mt.datacenter and t1.machine = mt.machine and t1.last_update = mt.last_update)