The most common method to fetch previous row value in SQL is by using the LAG function. The LAG function returns the previous row value. Depending on the order by clause the previous row is determined and the column value is returned.
/* how to get previous row value in sql - using LAG function */
select
catid,
lag(catid) over (order by catid) as previous_catid
from category;
| catid | previous_catid |
|---|---|
| 1 | NULL |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
| 6 | 5 |
| 7 | 6 |
| 8 | 7 |
| 9 | 8 |
| 10 | 9 |
| 11 | 10 |
You can also fetch previous row value without using the LAG function.
We will use MAX or MIN OLAP function and will add extra condition to it in ORDER BY clause.
The extra condition is "rows between 1 preceding and 1 preceding" which will fetch previous row value.
select
catid,
min(catid) over (order by catid rows between 1 preceding and 1 preceding) as previous_catid
from category;| catid | previous_catid |
|---|---|
| 1 | NULL |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
| 6 | 5 |
| 7 | 6 |
| 8 | 7 |
| 9 | 8 |
| 10 | 9 |
| 11 | 10 |
If you prefer watching video , please check the video below