How to reference two rows of data at a time in Oracle SQL?

Today I came across some interesting and useful functions in Oracle. The functions are LAG and LEAD. It is used to get the previous and next record in the table without any self join. Though these functions are there from version 8.1.6, i released its usage now only.


 ex)


01/01/2011 schema_a 100
01/02/2011 schema_a 105
01/03/2011 schema_a 115


Rate of change


01/02/2001 (105 -100) = 5
01/03/2001 (115 -105) = 10

SELECT date_column,schema_name,size_in_mb,

lag(size_in_mb) over(partition by schema_name order by date_column) prior_size_in_mb,size_in_mb - lag(size_in_mb) over(partition by schema_name order by date_column) change_in_mb FROM some_table_name

Similar functions are FIRST and LAST. Please refer http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php for further details.