aggregation - SQL Overwrite value in column in group by

Question

lets say i have following sql-table

fruit | amount | date
-------------------------
apple |    2   | 2019
apple |    3   | 2018
apple |    2   | 
peach |    2   | 
peach |    3   | 2017

where i want to fill up the null values in the column date with the earliest date of each fruit, so that the date column consists no null values. The result should look like this:

fruit | amount | date
-------------------------
apple |    2   | 2019
apple |    3   | 2018
apple |    2   | 2018
peach |    2   | 2017
peach |    3   | 2017

Any ideas how to do this in sql?

Answer 1

One method to do this sans analytic functions uses a correlated subquery:

SELECT
    fruit,
    amount,
    COALESCE(date, (SELECT MIN(date) FROM yourTable t2 WHERE t2.fruit = t1.fruit)) date
FROM yourTable t1;

We could also use an aggregation join approach:

SELECT
    t1.fruit,
    t1.amount,
    COALESCE(t1.date, t2.min_date) AS date
FROM yourTable t1
INNER JOIN
(
    SELECT fruit, MIN(date) AS min_date
    FROM yourTable
    GROUP BY fruit
) t2
    ON t2.fruit = t1.fruit;