Problem
How to access the “previous row” value in a SELECT statement in SQL?
Input
| id | sale_date | units_sold |
|---|---|---|
| 1 | 2023-01-01 | 10 |
| 2 | 2023-01-02 | 15 |
| 3 | 2023-01-03 | 12 |
| 4 | 2023-01-04 | 20 |
Try Hands-On: HERE
Source Tables: Gist
Desired Solution
| sale_date | current_day_sales | previous_day_sales |
|---|---|---|
| 2023-01-01 | 10 | |
| 2023-01-02 | 15 | 10 |
| 2023-01-03 | 12 | 15 |
| 2023-01-04 | 20 | 12 |
Solution 1:
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:
Using TempTables
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;
Explanation:
- The
LAG()function allows us to access data from a previous row in the result set without having to join the table to itself. -
The
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
Solution 2:
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;
Explanation:
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.
Solution 3:
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;
Explanation:
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.
