K SF

If you were to log in, you'd be able to get more information on your fellow community member.

Static Page Comments

• September 1, 2004, on Dates in Oracle:

  You state: >> no way in standard SQL to refer to "the value of this column from the previous row in the report". At least in Oracle 8i SQL, there is a way in to refer to this, I'm sure it isn't standard, but useful nonetheless, and so I present it here. It is called an Analytic Function. There are several, but the one demonstrated in this example is LAST_VALUE. SELECT r1, r2, r2 - r1 reg_gap FROM (SELECT u1.update_date AS r1, LAST_VALUE (update_date) OVER (ORDER BY update_date ASC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS r2 FROM users u1 WHERE u1.user_id > 100000) WHERE r1 <> r2 ORDER BY r1 From the inside out, I take the update_date from the users table, and using the LAST_VALUE function, I ask for the last update_date value, including in the window the current row and the next ...