How can relevant last values be obtained from a column and stored in a new column while considering a condition in SQL?

edit

Assuming that you want to get the last value of a column that satisfies a certain condition and store it in a new column, you can use a subquery to achieve this.

Here's an example query that uses a subquery to get the last value of the "price" column where the "productname" is "Apple" and stores it in a new column called "lastapple_price":

SELECT product_name, price, 
(SELECT price FROM your_table 
WHERE product_name = 'Apple' AND id < t.id ORDER BY id DESC LIMIT 1)
AS last_apple_price 
FROM your_table t

In this query, we are selecting the "productname" and "price" columns from the "yourtable" table, and using a subquery to get the last value of the "price" column where the "productname" is "Apple". The subquery filters the table by the "productname" and gets the last value by ordering the results by "id" in descending order and limiting the result to the first row. The subquery is aliased as "lastappleprice" and included as a new column in the main query.