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

    Unanswered: DB2 9.7 Fetch History ROWS

    I have a database table that holds the History of tag reads from RFID Tags.
    The main columns are: TAGID, LOCATION and LASTUPDATEDATE.
    There are some 20,000+ rows in the table and growing but only 12 unique tags.

    The requirement is to show each tag the last 3 locations for the tag and the date it was shown in that location, sorted by TAGID and newest of the 3 dates

    Any help would really be appreciated

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Consider to use ROW_NUMBER() OLAP specification with OVER(PARTITION BY tagid ORDER BY lastupdatedate DESC).

    See manual for detailes.
    OLAP specifications - IBM DB2 9.7 for Linux, UNIX, and Windows

    This example in the topic may be useful for your issue.
    •List the top five wage earners.
    Code:
       SELECT EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY 
         FROM (SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
           RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
           FROM EMPLOYEE) AS RANKED_EMPLOYEE 
         WHERE RANK_SALARY < 6 
         ORDER BY RANK_SALARY

  3. #3
    Join Date
    Jan 2012
    Posts
    20
    select id, tagid, credat, vgrpid from (select id, tagid, credat, vgrpid, row_number() over (partition by vgrpid order by credat desc) as rn from tagread) where rn = 1;

Posting Permissions

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