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.
