Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    40

    Unanswered: problem fetching rows with latest timestamp

    Hi,
    I have a collection which pulls the services running on servers every few minutes. I store in the table

    serverip, servicename, collectiontime, status, id

    where id maps to different servers.

    The collection time will be the same for a given collection against a server but vary from server to server. For example

    10.1.1.1 windows scheduler 11-01-2010 14:41.23 running
    10.1.1.1 windows service1 11-01-2010 14:41.23 running
    10.1.1.1 windows service2 11-01-2010 14:41.23 running
    10.1.1.2 windows scheduler 11-01-2010 14:33.23 running
    10.1.1.2 windows service1 11-01-2010 14:33.23 running
    10.1.1.2 windows service2 11-01-2010 14:33.23 running
    10.1.1.1 windows scheduler 11-01-2010 14:32.23 running
    10.1.1.1 windows service1 11-01-2010 14:32.23 running
    10.1.1.1 windows service2 11-01-2010 14:32.23 running

    I'm trying to construct a query which will retrieve all the services and their states from each server but only for the last collection time.

    select serverip, servicename, status, max(collectiontime)
    from services
    group by serverip, servicename, status

    which gives me multiple rows. My first attempt had
    'where collectiontime = (select max(collectiontime) from ..) ' which would work for a single server but not for them all since the collection times were not consistent.

    Any help here is greatly appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    select s1.serverip, s1.servicename, s1.status, s1.collectiontime
    from services s1
    where collectiontime = (select max(s2.collectiontime) 
                            from services s2
                            where s2.serverip = s1.serverip
                              and s2.servicename = s1.servicename
                              and s2.status = s1.status)

  3. #3
    Join Date
    Feb 2010
    Posts
    40
    Thanks, I knew I was missing something. I was actually able to only use where s2.serverip = s1.serverip because all services, states have the same collection time for a given server and collection.

Posting Permissions

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