I have these columns: type(char), date , price. I would like to check if the same type’s price has not changed in the previous record.
I tried this for start:
SELECT * FROM table t1, table t2 WHERE t1.date > t2.date and t1.type = t2.type
I only want to check for the date prior not for every date that is smaller.
This table stores price changes.
product_type | change_date | price |
---|---|---|
‘A’ | 2020.02.18 | 500 |
‘A’ | 2020.02.20 | 750 |
‘A’ | 2020.02.22 | 800 |
‘B’ | 2020.02.22 | 500 |
‘B’ | 2020.02.27 | 900 |
‘C’ | 2020.02.22 | 400 |
‘C’ | 2020.02.29 | 700 |
I want to put this table next to it self to see if the price remained unchanged like this:
product_type | change_date | price | product_type | change_date | price |
---|---|---|---|---|---|
‘A’ | 2020.02.20 | 750 | ‘A’ | 2020.02.18 | 500 |
‘A’ | 2020.02.22 | 800 | ‘A’ | 2020.02.20 | 750 |
‘B’ | 2020.02.27 | 900 | ‘B’ | 2020.02.22 | 500 |
‘C’ | 2020.02.29 | 700 | ‘C’ | 2020.02.22 | 400 |
I only want to display rows that show no price change, so price = previous price
.
Advertisement
Answer
Try this:
select t.*, lag(price) over(partition by type order by date) as previous_price from table t