How to access the “previous row” value in a SELECT statement in SQL?
Try Hands-On: HERE
Source Tables: Gist
To access the previous row value in a SELECT statement in MySQL, we can make use of the LAG() window function. Here’s how to do that:
SELECT sale_date, units_sold AS current_day_sales, LAG(units_sold) OVER (ORDER BY sale_date) AS previous_day_sales FROM sales ORDER BY sale_date;Table1;
LAG()function allows us to access data from a previous row in the result set without having to join the table to itself.
OVER (ORDER BY sale_date) clause specifies the order in which to process the rows of the table, which is necessary for determining the “previous” row.
The result of the above query will display each day’s sales alongside the sales from the previous da
If for some reason you can’t use the LAG() function (for instance, if you’re working with an older version of MySQL that doesn’t support window functions), you can use a self-join to accomplish the same result.
Step 1: Create an alias for the same table and join them on a condition that matches the current row with the previous row.
Step 2: Use the joined table to select the previous row’s value.
SELECT s1.sale_date, s1.units_sold AS current_day_sales, s2.units_sold AS previous_day_sales FROM sales s1 LEFT JOIN sales s2 ON s1.id = s2.id + 1 ORDER BY s1.sale_date;
We use a LEFT JOIN to join the table with itself (s1 and s2 are just aliases for the same sales table).
The condition s1.id = s2.id + 1 matches each row with the previous row, assuming that id is a sequentially increasing value (as defined by the AUTO_INCREMENT attribute).
The result of this query will be the same as the previous solution: each day’s sales alongside the sales from the previous day.
Keep in mind, this approach assumes that the id column has sequential integers without any gaps. If there are gaps or the id values are not sequential, this method won’t produce the correct results.
Step 1: For each row in the sales table, execute a subquery to fetch the most recent sales data prior to the current row’s date.
Step 2: Present the current row’s data alongside the results from the subquery.
SELECT s1.sale_date, s1.units_sold AS current_day_sales, ( SELECT s2.units_sold FROM sales s2 WHERE s2.sale_date < s1.sale_date ORDER BY s2.sale_date DESC LIMIT 1 ) AS previous_day_sales FROM sales s1 ORDER BY s1.sale_date;
For every row (date) in the table, the subquery looks for the most recent sale that occurred before the current date by filtering rows where s2.sale_date < s1.sale_date.
The ORDER BY s2.sale_date DESC sorts the matching rows in descending order, and LIMIT 1 ensures that only the most recent (i.e., the previous day’s) sale is fetched.