How to Compare Previous and Current Rows in SQL

One of the easiest ways, to compare this is using the lag function. The lag function will allow you to shift the rows downward so that you can view these rows as one observational row. Here is a simple example of how to use the lag function to shift rows downward.

However let’s take a quick look at the original table in question so that we can understand the data structure.

select
orderid, 
quantity ,
lag(quantity,1) over(order by orderid) as lag
from orderdetails

We are shifting the quality column downward by 1 with the LAG(quantity,1) function. The OVER(order by orderid) tells the function to perform the function over the order id and preserve the structure of this. So our result will be the table below:

Feel free to watch the video of how this is done on YouTube.

Gaelim Holland

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments