SQLite 3.27

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

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