Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Unanswered: Compare previous record in db2

    Hello,
    Need some quick help..

    I need to fetch the previous record and the current record ( in predicate ) in a single query of DB2...

    Can somebody help me how it can be done!!!

    Thanks for your help !!!

    Thanks
    Thamin

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to provide a lot more information on what you are trying to do. There is no such thing as "previous record" in DB2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you give an example? Also useful will be the background of the requirement



    --
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jul 2011
    Posts
    13
    For Example :

    I have a table TAB1 like this

    Name dept exp
    AAA 123 4
    DDD 321 6
    KKK 456 7
    LLL 555 5
    SSS 888 5

    Now I have a select statement
    SELECT NAME FROM TAB1 WHERE NAME='LLL'

    I need to retrieve both LLL and KKK record

    Thanks,
    Thamizh

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    As Marcus mentioned earlier, there is no concept of previous record in DB2. You have to use some logic to say what a previous record is ...
    For eg, in your sample data, it could be order by on Name column . you can use row number to get the records in this case or even
    select a.* from tab1 a where name<='LLL' order by name desc fetch first 2 rows only

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    you can use the lag function to get the fields from the previous statement

    Code:
    lag( name) over( order by field_list )
    you can find examples in docu "SQL Reference 1 ".

    But your select has no previous statements because

    WHERE NAME='LLL'

    finds only 1 record according to your example. If your result set has more than 1 record you can use lag for previous and lead for the next record in the record set.

Posting Permissions

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