replace null values in subsequent rows from previous row

Question

I have this table about foods and rating and facing this scenario.

select * from foods

food_type	country	rating
Pizza	Italy	8
Pizza	Italy	null
Pizza	Italy	null
Pizza	Sweden	6
Pizza	Sweden	null
Pizza	Sweden	null
Calzone	France	7
Calzone	France	null
Calzone	France	null
Hero Sub	USA	10
Hero Sub	USA	null
Hero Sub	USA	null
Hero Sub	USA	null

How can I replace the nulls with the rating value of the same food_type and same country from the previous not null rating value? e.g. new_rating column

food_type	country	rating	new_rating
Pizza	Italy	8	8
Pizza	Italy	null	8
Pizza	Italy	null	8
Pizza	Sweden	6	6
Pizza	Sweden	null	6
Pizza	Sweden	null	6
Calzone	France	7	7
Calzone	France	null	7
Calzone	France	null	7
Hero Sub	USA	10	10
Hero Sub	USA	null	10
Hero Sub	USA	null	10
Hero Sub	USA	null	10

I am using postgreSQL 11.4!

LukStorms · Accepted Answer · 2021-12-06 23:15:34Z

1

Try a coalesce'd MAX OVER

SELECT food_type, country, rating
, COALESCE(rating, MAX(rating) OVER (PARTITION BY food_type, country)) AS new_rating
FROM foods

answered Dec 6, 2021 at 23:15

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

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

Comments

Andreas Hauser · Accepted Answer · 2021-12-06 23:05:58Z

0

You can try a Subquery in the SELECT-Part - like this:

https://www.w3resource.com/sql/subqueries/understanding-sql-subqueries.php

select 
  master.*,
  (select 
    newfood.rating 
   from 
    foods newfood 
   where 
    newfood.food_type = master.food_type AND newfood.country = master.country
    AND newfood.rating is not NULL
   group by newfood.rating
  ) as new_rating
from 
  foods master
;

answered Dec 6, 2021 at 23:05

Andreas Hauser

928 bronze badges

Collectives™ on Stack Overflow

replace null values in subsequent rows from previous row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related