How to Get Previous Row Value in Oracle?

In Oracle, use the lag() analytical function to get the previous row value. Below are some examples:

Lag Syntax

lag(expr, offset, defaut_value) over (partition_clause order_by_clause)

For the expr, you will provide the column name, the offset is the previous row number from which to get the value and the default_value is for the current row value.

And for the over window function, you will specify the partition by clause and the order by clause. By looking at the below example you will understand much.

Get the Previous Row Value in Oracle SQL Query

While running the query the lag() analytical function can get the value from any column from the previous row. This sometimes required to do many different tasks and it is useful.

In the following example, it will get the data from the EMP table and will show the salary of the employee from the previous row. For the first row, it will display 0 and this is obvious because there is no previous row for the first row. But for the second row, it will display the salary of the previous row:

select ename, job, sal, 
       lag(sal, 1, 0) over (order by ename) previous_sal
               from emp;

Output:

ENAME	JOB	SAL	PREVIOUS_SAL
ADAMS	CLERK	1100	0
ALLEN	SALESMAN	1600	1100
BLAKE	MANAGER	2850	1600
CLARK	MANAGER	2450	2850
FORD	ANALYST	3000	2450

You can see in the above result set, that the column previous_sal is containing the previous row value.

See also:

• Get Previous Month's Data in Oracle
• Get Previous Record Value in Oracle Forms
• Get Next Row Value in Oracle