In Oracle, use the
lag() analytical function to get the previous row value. Below are some examples:
lag(expr, offset, defaut_value) over (partition_clause order_by_clause)
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;
You can see in the above result set, that the column
previous_sal is containing the previous row value.
- Get Previous Month's Data in Oracle
- Get Previous Record Value in Oracle Forms
- Get Next Row Value in Oracle