At least for my ability anyway!

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!

Ian

Ian,
Something like this should be what you need:

Select datacenter,machine,max(last_update) from mytable group by datacenter,machine

Andy

Thanks Andy.

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.

Thanks,
Ian

Do the uptime values change with the last_update column?

Andy

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.

Thanks,
Ian

OK, try something like this:

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)

HTH

Andy

Thanks Andy, that's perfect!