how to fill missing values in table using sql for window function

Question

order_id	Products	Country
MB-123	Bread	US
MB-123	Milk
MB-1256	Cheese	UK
MB-1256	Tomato Sauce
MB-1256	Milk

The missing values in the above table needs to be filled with the same country names for the same order_id.

I tried with coalesce with window function but I am not able to fill the null value for each orderid. I want null values in country column to be filled by the country name for the same order_id.

I want the desired output as the table shown below :-

order_id	Products	Country
MB-123	Bread	US
MB-123	Milk	US
MB-1256	Cheese	UK
MB-1256	Tomato Sauce	UK
MB-1256	Milk	UK

Answer 1

In PostgreSQL, you can use the MAX window function, that allows you to get the non-null value for each "order_id" partition.

SELECT order_id, Products, MAX(Country) OVER(PARTITION BY order_id) AS Country 
FROM tab

If you need to update the existing table, you can compute the maximum value for each country, then apply a JOIN operation inside the UPDATE` statement:

WITH cte AS (
    SELECT order_id, Products, MAX(Country) OVER(PARTITION BY order_id) AS Country 
    FROM tab
)
UPDATE tab 
SET Country = cte.Country
FROM cte
WHERE cte.order_id = tab.order_id AND cte.Products = tab.products
  AND tab.Country IS NULL

Check the demo here.