By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test
(
pid int,
pdate date,
BMI decimal(4,1)
)
✓
insert into test (pid, pdate, BMI) values
(1, '2000-01-01', NULL)
, (1, '2003-05-01', 18.5)
, (1, '2002-07-15', 24.9)
, (2, '2009-09-25', NULL)
, (2, '2015-04-18', 21.7)
;
✓
select *
, first_value(BMI) over (partition by pid order by case when BMI is not null then 1 else 2 end, date(pdate)) as firstBMI
from test
order by pid, pdate
| pid | pdate | BMI | firstBMI |
|---|---|---|---|
| 1 | 2000-01-01 | null | 24.9 |
| 1 | 2002-07-15 | 24.9 | 24.9 |
| 1 | 2003-05-01 | 18.5 | 24.9 |
| 2 | 2009-09-25 | null | 21.7 |
| 2 | 2015-04-18 | 21.7 | 21.7 |