How to get previous row value in sql ?

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;
catidprevious_catid
1NULL
21
32
43
54
65
76
87
98
109
1110

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;
catidprevious_catid
1NULL
21
32
43
54
65
76
87
98
109
1110

If you prefer watching video , please check the video below

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *