Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: DB2 9.7 FETCH LAST ROW when value changes

    I have a table of values from tag reads example below
    What I want to do is show the last date when the tag was in a particular zone so I want to output records 4,7 and 10.
    I am a little unsure how to structure the query so appreciate any assistance
    I have changed my table as when I previously had ID which doesn't actually exist I just put it in to show which rows I wanted.
    So the query should show WHERE TAGID = 'IRCODE00056394' as there are 12 tags but what the requirement is. SHOW the last time it was seen in a particular ZONE.
    So I only want to show the last 7 days and each time it changed a zone what was the last time it was seen in the zone. So once it enters the zone then show the last time it was in the zone before it went to another zone.
    I am guessing somehow I need to hold the last zone as a variable and then compare the next read
    Thanks again for your help


    TAGID CREDAT ZONEID

    TAG56394 2012-04-29-15-10.14.332000 Zone01
    TAG56394 2012-04-29-15-12.14.332000 Zone01
    TAG56394 2012-04-29-15-14.14.332000 Zone01
    TAG56394 2012-04-29-15-10.16.332000 Zone01
    TAG56394 2012-04-29-15-10.18.332000 Zone02
    TAG56394 2012-04-29-15-10.20.332000 Zone02
    TAG56394 2012-04-29-15-10.22.332000 Zone02
    TAG56394 2012-04-29-15-10.24.332000 Zone04
    TAG56394 2012-04-29-15-10.26.332000 Zone04
    TAG56394 2012-04-29-15-10.28.332000 Zone04
    Last edited by gcrooks; 02-05-13 at 07:01. Reason: Add more clarification

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    One way might be to use LEAD OLAP specification.
    OLAP specifications - IBM DB2 9.7 for Linux, UNIX, and Windows

  3. #3
    Join Date
    Jan 2012
    Posts
    20
    select max(id) from tagread group by vgrpid;

Posting Permissions

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