Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    24

    Unanswered: Does DB2 UDB 7.2 have RRN fuction?

    Hi there,

    Juz wonder whether in DB2 UDB 7.2 for windows does have the RRN function?

    actually my objective is :

    find the last 5 pervious row in the record.

    eg: in the calendar table (without saturday,sunday n public holiday --> since this calendar table is used for stock exchange)

    let say today is 24 Feb 2005, the last 5 trading day is 18 Feb 2004.

    if not using RRN what othe function i can use in DB2 to achieve this?
    Hello

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Why don't you post the DDL for your calendar table and give some examples of how the table is populated with data.
    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
    Mar 2004
    Posts
    24

    Post Sample data

    D_Trade L5_D_Trade
    2004-02-01 2004-01-26
    2004-02-02 2004-01-27
    2004-02-03 2004-01-28
    2004-02-04 2004-01-31
    2004-02-07 2004-02-01
    2004-02-08 2004-02-02
    ... ...
    ... ...
    ... ...

    This is how the final tables looks like which need to get from CALENDAR TABLE.

    Below is the sample data for calendar :

    SELECT *
    FROM SIMS.CALENDAR
    WHERE YEAR(D_TRADE) = 2004
    AND MONTH(D_TRADE) in (1,2)
    ORDER BY D_TRADE


    D_TRADE
    2004-01-02
    2004-01-05
    2004-01-06
    2004-01-07
    2004-01-08
    2004-01-09
    2004-01-12
    2004-01-13
    2004-01-14
    2004-01-15
    2004-01-16
    2004-01-19
    2004-01-20
    2004-01-21
    2004-01-22
    2004-01-23
    2004-01-26
    2004-01-27
    2004-01-28
    2004-01-29
    2004-01-30
    2004-02-02
    2004-02-03
    2004-02-04
    2004-02-05
    2004-02-06
    2004-02-09
    2004-02-10
    2004-02-11
    2004-02-12
    2004-02-13
    2004-02-16
    2004-02-17
    2004-02-18
    2004-02-19
    2004-02-20
    2004-02-23
    2004-02-24
    2004-02-25
    2004-02-26
    2004-02-27

    please refer the attachement for the full detail. thanks
    Attached Files Attached Files
    Hello

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    One option could be to use the row_number function ... Check the SQL Reference for details
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    select d_trade
    from
    (select d_trade, rownumber() over
    (order by d_trade desc)
    as RN from db2inst1.calendar where d_trade <= '2004-02-24' fetch first 5 rows only)
    as TR where RN = 5
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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