Analytic Function LAG/LEAD

lag is an analytical function that can be used to get the value of a column in a previous row.

If you want to retrieve the value of the next row, use lead instead of lag.

Because the functions provide access to more than one row of a table at the same time without a self-join, they can enhance processing speed.

{LAG | LEAD} ( value_expr [, offset] [, default] ) 
   OVER ( [query_partition_clause] order_by_clause )

The LAG function provides access to a row at a given offset prior to the current position, and the LEAD function provides access to a row at a given offset after the current position.

The [query_partition_clause] give a reliable relative positions of rows.

The example schema SH is used.

SELECT CALENDAR_MONTH_DESC, 
       sum(quantity_sold), 
       lag(sum(quantity_sold),1) over (ORDER BY CALENDAR_MONTH_DESC) "Previous Month" 
FROM 
       SH.sales,
       SH.TIMES
WHERE
       SH.times.TIME_ID = SH.sales.TIME_ID AND
       SH.times.CALENDAR_YEAR = '2000'
GROUP BY CALENDAR_MONTH_DESC
ORDER BY CALENDAR_MONTH_DESC ASC

And you get :