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?

Why don't you post the DDL for your calendar table and give some examples of how the table is populated with 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

One option could be to use the row_number function ... Check the SQL Reference for details

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