I have the following table in Oracle SQL:
+-------------------+--------------+---------------+-----------------+-------------------+--------+ | OWNER_CHANGE_DATE | CAR | LICENSE_PLATE | OWNER_NAME | COLOR_CHANGE_DATE | COLOR | +-------------------+--------------+---------------+-----------------+-------------------+--------+ | 1-Jan-20 | Ford F150 | 3892A84 | John Doe | 2-Feb-20 | red | | 1-Jan-20 | Ford F150 | 3892A84 | John Doe | 21-Mar-20 | orange | | 1-Jan-20 | Ford F150 | 3892A84 | John Doe | 22-Mar-20 | green | | 23-Mar-20 | Ford F150 | 3892A84 | Jane Doe | 23-Mar-20 | blue | | 23-Mar-20 | Ford F150 | 3892A84 | Jane Doe | 24-Mar-20 | pink | | 23-Mar-20 | Ford F150 | 3892A84 | Jane Doe | 25-Mar-20 | purple | | 23-Mar-20 | Ford F150 | 3892A84 | Jane Doe | 26-Mar-20 | brown | | 2-Jun-20 | Ford F150 | 3892A84 | Jack Doe | (null) | (null) | | 12-Jul-20 | Ford F150 | 3892A84 | Steve Doe | (null) | (null) | | 1-Jan-19 | Toyota Camry | 894A839 | Adam Baker | 1-Jan-19 | red | | 1-Jan-19 | Toyota Camry | 894A839 | Adam Baker | 5-Jan-19 | blue | | 3-Feb-19 | Toyota Camry | 894A839 | Frank Evans | 14-Feb-19 | red | | 19-Apr-19 | Toyota Camry | 894A839 | Clark Davis | (null) | (null) | | 11-Aug-19 | Toyota Camry | 894A839 | Joe Bryant | (null) | (null) | | 1-Oct-19 | Toyota Camry | 894A839 | Jennifer Smith | (null) | (null) | | 30-Nov-19 | Toyota Camry | 894A839 | William Johnson | (null) | (null) | +-------------------+--------------+---------------+-----------------+-------------------+--------+
Notice that there are six rows with NULL values for COLOR_CHANGE_DATE and COLOR. I want to fill those values with the latest color and date based on car. For example, once I fill in the NULL values, I want the resulting table to look like the following:
+-------------------+--------------+---------------+-----------------+-------------------+--------+ | OWNER_CHANGE_DATE | CAR | LICENSE_PLATE | OWNER_NAME | COLOR_CHANGE_DATE | COLOR | +-------------------+--------------+---------------+-----------------+-------------------+--------+ | 1-Jan-20 | Ford F150 | 3892A84 | John Doe | 2-Feb-20 | red | | 1-Jan-20 | Ford F150 | 3892A84 | John Doe | 21-Mar-20 | orange | | 1-Jan-20 | Ford F150 | 3892A84 | John Doe | 22-Mar-20 | green | | 23-Mar-20 | Ford F150 | 3892A84 | Jane Doe | 23-Mar-20 | blue | | 23-Mar-20 | Ford F150 | 3892A84 | Jane Doe | 24-Mar-20 | pink | | 23-Mar-20 | Ford F150 | 3892A84 | Jane Doe | 25-Mar-20 | purple | | 23-Mar-20 | Ford F150 | 3892A84 | Jane Doe | 26-Mar-20 | brown | | 2-Jun-20 | Ford F150 | 3892A84 | Jack Doe | 26-Mar-20 | brown | | 12-Jul-20 | Ford F150 | 3892A84 | Steve Doe | 26-Mar-20 | brown | | 1-Jan-19 | Toyota Camry | 894A839 | Adam Baker | 1-Jan-19 | red | | 1-Jan-19 | Toyota Camry | 894A839 | Adam Baker | 5-Jan-19 | blue | | 3-Feb-19 | Toyota Camry | 894A839 | Frank Evans | 14-Feb-19 | red | | 19-Apr-19 | Toyota Camry | 894A839 | Clark Davis | 14-Feb-19 | red | | 11-Aug-19 | Toyota Camry | 894A839 | Joe Bryant | 14-Feb-19 | red | | 1-Oct-19 | Toyota Camry | 894A839 | Jennifer Smith | 14-Feb-19 | red | | 30-Nov-19 | Toyota Camry | 894A839 | William Johnson | 14-Feb-19 | red | +-------------------+--------------+---------------+-----------------+-------------------+--------+
Note that the NULL values for the six rows have been filled in with the latest COLOR_CHANGE_DATE and COLOR for each CAR. That is, for Ford F150, the NULL values are replaced with the latest COLOR_CHANGE_DATE= 26-Mar-20 and COLOR = brown.
There can be indefinite number of owners that do not have corresponding COLOR and COLOR_CHANGE_DATEs. If an owner does not have COLOR and COLOR_CHANGE_DATEs, then I need to fill in the NULL values with the latest COLOR_CHANGE_DATE and COLOR for each car.
Can you show me how to do this in Oracle SQL?
Below is the SQL query to generate the sample table for your convenience.
with a as (
select to_date('1/1/2020 11:51', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Ford F150' as car
, '3892A84' as license_plate
, 'John Doe' as owner_name
, to_date('2/2/2020 12:43', 'MM/DD/YYYY HH24:MI') color_change_date
, 'red' color
from dual
union
select to_date('1/1/2020 11:51', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Ford F150' as car
, '3892A84' as license_plate
, 'John Doe' as owner_name
, to_date('3/21/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
, 'orange' color
from dual
union
select to_date('1/1/2020 11:51', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Ford F150' as car
, '3892A84' as license_plate
, 'John Doe' as owner_name
, to_date('3/22/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
, 'green' color
from dual
union
select to_date('3/23/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Ford F150' as car
, '3892A84' as license_plate
, 'Jane Doe' as owner_name
, to_date('3/23/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
, 'blue' color
from dual
union
select to_date('3/23/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Ford F150' as car
, '3892A84' as license_plate
, 'Jane Doe' as owner_name
, to_date('3/24/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
, 'pink' color
from dual
union
select to_date('3/23/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Ford F150' as car
, '3892A84' as license_plate
, 'Jane Doe' as owner_name
, to_date('3/25/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
, 'purple' color
from dual
union
select to_date('3/23/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Ford F150' as car
, '3892A84' as license_plate
, 'Jane Doe' as owner_name
, to_date('3/26/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
, 'brown' color
from dual
union
select to_date('6/2/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Ford F150' as car
, '3892A84' as license_plate
, 'Jack Doe' as owner_name
, null color_change_date
, null color
from dual
union
select to_date('7/12/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Ford F150' as car
, '3892A84' as license_plate
, 'Steve Doe' as owner_name
, null color_change_date
, null color
from dual
union
select to_date('1/1/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Toyota Camry' as car
, '894A839' as license_plate
, 'Adam Baker' as owner_name
, to_date('1/1/2019 11:51', 'MM/DD/YYYY HH24:MI') color_change_date
, 'red' color
from dual
union
select to_date('1/1/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Toyota Camry' as car
, '894A839' as license_plate
, 'Adam Baker' as owner_name
, to_date('1/5/2019 11:51', 'MM/DD/YYYY HH24:MI') color_change_date
, 'blue' color
from dual
union
select to_date('2/3/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Toyota Camry' as car
, '894A839' as license_plate
, 'Frank Evans' as owner_name
, to_date('2/14/2019 11:51', 'MM/DD/YYYY HH24:MI') color_change_date
, 'red' color
from dual
union
select to_date('4/19/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Toyota Camry' as car
, '894A839' as license_plate
, 'Clark Davis' as owner_name
, null color_change_date
, null color
from dual
union
select to_date('8/11/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Toyota Camry' as car
, '894A839' as license_plate
, 'Joe Bryant' as owner_name
, null color_change_date
, null color
from dual
union
select to_date('10/1/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Toyota Camry' as car
, '894A839' as license_plate
, 'Jennifer Smith' as owner_name
, null color_change_date
, null color
from dual
union
select to_date('11/30/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
, 'Toyota Camry' as car
, '894A839' as license_plate
, 'William Johnson' as owner_name
, null color_change_date
, null color
from dual
)
select *
from a
order by car, owner_change_date, color_change_date
Any help would be greatly appreciated!
Advertisement
Answer
Oracle supports ignore nulls in window functions, so you can just do:
select
a.*,
coalesce(
color,
lag(color) ignore nulls over(partition by car order by color_change_date)
) color2
from a