select the last filled row before a given timestamp

Hi all,

i'd appreciate if anyone could tell me how to select the last filled
row of a table before a given timestamp (date).

E.g. if the timestamp has date 20061101 i need to select the row with
the timestamp right before the given one. Eg if I the last filled row
before 20061101 has timestamp 20061025 (but of course I dont know which
timestamp it is), how could i code this in db2 sql to select it?

thanx a lot, help is appreciated
regards, thomas

Re: select the last filled row before a given timestamp

thomas.naegeli@gmail.com wrote:
> Hi all,
>
> i'd appreciate if anyone could tell me how to select the last filled
> row of a table before a given timestamp (date).
>
> E.g. if the timestamp has date 20061101 i need to select the row with
> the timestamp right before the given one. Eg if I the last filled row
> before 20061101 has timestamp 20061025 (but of course I dont know which
> timestamp it is), how could i code this in db2 sql to select it?
>
> thanx a lot, help is appreciated
> regards, thomas

Well this depends on what version of DB2 you are using. I think the
query will be something like one of:

SELECT * from table
WHERE date < '2006-11-01'
ORDER BY date DESC
FETCH FIRST ROW ONLY

or

SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY date DESC) AS rownumber,
*
FROM table
WHERE date < '2006-11-01'
) AS foo
WHERE rownumber = 1