I have the following data in SQL.
NAME,DATE,REF Pat1,2021-07-15,5072 '',NULL,5072 '',NULL,5072 '',NULL,5072 '',NULL,5072 Pat2,2021-07-15,5073
Is there a way using SELECT QUERY that we can replace the NULL values in DATE column based on the REF values?
Like replace the NULL values with the first available date for matching REF value, without making any change to the database.
Expected Result
NAME,DATE,REF Pat1,2021-07-15,5072 '',2021-07-15,5072 '',2021-07-15,5072 '',2021-07-15,5072 '',2021-07-15,5072 Pat2,2021-07-15,5073
Advertisement
Answer
You can do it with MAX() window function:
SELECT NAME,
COALESCE(DATE, MAX(DATE) OVER (PARTITION BY REF)) DATE,
REF
FROM tablename
See the demo.