CODEWITHSUNDEEP
sqlselectamazon-redshiftlag
xieeerrWW
xieeerrWW

Reputation: 3

Lag function - issue

I have data like that :

Year Value
2022 1000
2023 500

and I want to create column with previous values like that:

Year Value Value_PY
2022 1000 null
2023 500 1000
2024 null 500

The LAG() function isn't the best solution for this because I get the table without 2024 row. How can I solve this?

lag(value) OVER(order by Year)

How can I solve this? What should I add to this LAG() function?

Upvotes: 0

Views: 35

Answers (1)

Bart McEndree
Bart McEndree

Reputation: 3281

Add a row for 2024. Union with a select '2024' as Year, NULL as value. Then apply your lag column.

SELECT *, lag(value) OVER(order by Year) AS Value_PY FROM
(
SELECT * FROM example
UNION 
SELECT '2024' AS Year, NULL AS value
) t
Year Value Value_PY
2022 1000 null
2023 500 1000
2024 null 500

Upvotes: 0

Related Questions