Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    16

    Unanswered: Nightmare Select Statement!

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Ian,
    Something like this should be what you need:

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

    Andy

  3. #3
    Join Date
    Sep 2005
    Posts
    16
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Do the uptime values change with the last_update column?

    Andy

  5. #5
    Join Date
    Sep 2005
    Posts
    16
    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

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Sep 2005
    Posts
    16

    Talking Thank you :)

    Thanks Andy, that's perfect!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •