Table:
I have a road_condition_test table:
create table road_condition_test (
road_id int,
age int,
condition number(10,1)
);
insert into road_condition_test (road_id,age,condition) values ('500080',0,20);
insert into road_condition_test (road_id,age,condition) values ('500080',1,20);
insert into road_condition_test (road_id,age,condition) values ('500080',2,20);
insert into road_condition_test (road_id,age,condition) values ('500080',3,20);
insert into road_condition_test (road_id,age,condition) values ('500080',4,19.6);
insert into road_condition_test (road_id,age,condition) values ('500080',5,19.2);
insert into road_condition_test (road_id,age,condition) values ('500080',6,18.8);
insert into road_condition_test (road_id,age,condition) values ('500080',7,18.4);
insert into road_condition_test (road_id,age,condition) values ('500080',8,18);
insert into road_condition_test (road_id,age,condition) values ('500080',9,18);
insert into road_condition_test (road_id,age,condition) values ('500080',10,18);
insert into road_condition_test (road_id,age,condition) values ('500080',11,18);
insert into road_condition_test (road_id,age,condition) values ('500080',12,null);
insert into road_condition_test (road_id,age,condition) values ('500080',13,null);
insert into road_condition_test (road_id,age,condition) values ('500080',14,null);
insert into road_condition_test (road_id,age,condition) values ('500080',15,null);
insert into road_condition_test (road_id,age,condition) values ('500100',0,18);
insert into road_condition_test (road_id,age,condition) values ('500100',1,18.3);
insert into road_condition_test (road_id,age,condition) values ('500100',2,18.6);
insert into road_condition_test (road_id,age,condition) values ('500100',3,19);
insert into road_condition_test (road_id,age,condition) values ('500100',4,19);
insert into road_condition_test (road_id,age,condition) values ('500100',5,19);
insert into road_condition_test (road_id,age,condition) values ('500100',6,19);
insert into road_condition_test (road_id,age,condition) values ('500100',7,null);
insert into road_condition_test (road_id,age,condition) values ('500100',8,null);
insert into road_condition_test (road_id,age,condition) values ('500100',9,null);
insert into road_condition_test (road_id,age,condition) values ('500100',10,null);
insert into road_condition_test (road_id,age,condition) values ('500100',11,null);
insert into road_condition_test (road_id,age,condition) values ('500100',12,null);
insert into road_condition_test (road_id,age,condition) values ('500100',13,null);
insert into road_condition_test (road_id,age,condition) values ('500100',14,null);
insert into road_condition_test (road_id,age,condition) values ('500100',15,null);
commit;
ROAD_ID AGE CONDITION
---------- ---------- ----------
500080 0 20
500080 1 20
500080 2 20
500080 3 20
500080 4 19.6
500080 5 19.2
500080 6 18.8
500080 7 18.4
500080 8 18
500080 9 18
500080 10 18
500080 11 18
500080 12
500080 13
500080 14
500080 15
500100 0 18
500100 1 18.3
500100 2 18.6
500100 3 19
500100 4 19
500100 5 19
500100 6 19
500100 7
500100 8
500100 9
500100 10
500100 11
500100 12
500100 13
500100 14
500100 15
Question:
I want to write a query that has a column called latest_known_condition.
For each road, the latest_known_condition column would contain the latest condition (according to age) that is not null.
Note: The latest condition is not to be confused with the minimum condition. Due to A) irregularities in inspections, and/or B) repairs to roads, the condition does not strictly go down over time (example: road 500100).
The column would only be populated for rows including and after the latest known condition.
ROAD_ID AGE CONDITION LATEST_KNOWN_CONDITION
---------- ---------- ---------- --------------------
500080 0 20
500080 1 20
500080 2 20
500080 3 20
500080 4 19.6
500080 5 19.2
500080 6 18.8
500080 7 18.4
500080 8 18
500080 9 18
500080 10 18
500080 11 18 18
500080 12 18
500080 13 18
500080 14 18
500080 15 18
500100 0 18
500100 1 18.3
500100 2 18.6
500100 3 19
500100 4 19
500100 5 19
500100 6 19 19
500100 7 19
500100 8 19
500100 9 19
500100 10 19
500100 11 19
500100 11 19
500100 12 19
500100 13 19
500100 14 19
500100 15 19
How can I do this?
Best Answer