Difficulties trying to replace null values with values from previous row in snowflake using sql

Question

Please I have a table, with null values and I am trying to write an sql query to replace them with values from the previous row

table: customer

id	value
001	34
002	Null
003	Null
004	Null
005	20
006	Null
007	Null
008	55

what I want to achieve

id	value
001	34
002	20
003	20
004	20
005	20
006	55
007	55
008	55

thanks

in relational databases there is no inherent notion of previous row unless the table is sorted somehow. So how do you define the previous row? — Zakaria
– Zakaria, Commented Feb 14, 2022 at 14:55
lets assume the table is sorted in descending order, I will update it now — Abiodun Adeoye
– Abiodun Adeoye, Commented Feb 14, 2022 at 14:58

Lukasz Szozda · Accepted Answer · 2022-02-14 17:38:16Z

4

Using LAG with IGNORE NULLS

SELECT *, COALESCE(value, LAG(value) IGNORE NULLS OVER(ORDER BY id)) AS value
FROM customer
ORDER BY id;

LAG(previous)/LEAD(following) value

db<>fiddle demo

Output:

edited Feb 14, 2022 at 17:38

answered Feb 14, 2022 at 15:07

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Abiodun Adeoye Over a year ago

thanks for the contribution, please can you help look into this? stackoverflow.com/questions/71111997/…

Zakaria Over a year ago

MySQL is far behind other RDBMS, didn't even know this existed. +1

Abiodun Adeoye Over a year ago

@Lukasz, please this didnt seem to work properly especially for the once with 3 NULL values above them

Lukasz Szozda Over a year ago

@lee Please find attached demo. Here you could use LEAD or LAG but ORDER BY has to be DESC. If this operation has to be done per specific group then OVER(PARTION BY ... ORDER BY id)

Zakaria · Accepted Answer · 2022-02-14 15:04:33Z

0

A subquery to get the values from the immediate following row where value is not null should do the job:

select id,
(select value from customer where id >= c.id and value is not null
order by id limit 1) as value
from customer c
order by id

answered Feb 14, 2022 at 15:04

Zakaria

4,7762 gold badges8 silver badges37 bronze badges

Collectives™ on Stack Overflow

Difficulties trying to replace null values with values from previous row in snowflake using sql

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related