CODEWITHSUNDEEP
sqlsqlitenull
Prajwal Mani Pradhan
Prajwal Mani Pradhan

Reputation: 397

How to get the first non-NULL value in SQL?

I have three columns of data. One column has ID and the second column has a date and the third column has BMI value. I want to create a fourth column that has the first_value based on date(ascending order) from the third column which is not null or avoiding the null.

So far, I have tried first_value in plain form which didn't work. I tried subsetting first_value inside the case when statement as 

CASE 
  WHEN BMI IS NOT NULL THEN (FIRST_VALUE(BMI) OVER PARTITION BY PID ORDER BY DATE)) 
  ELSE 0 
END AS FIRSTNOTNULLVALUE_BMI

gave me 0s. 

id    date         BMI 
1   2000-01-01    NULL
1   2003-05-01    18.1
1   2002-07-15    25.8
2   2009-09-25    NULL
2   2015-04-18    23.5

Any suggestions??

Upvotes: 4

Views: 8906

Answers (4)

LukStorms
LukStorms

Reputation: 29677

You can put that CASE in the ORDER BY of the FIRST_VALUE.
Then the null's will be sorted last for that function.

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

db<>fiddle here

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1270553

You could just use a subquery:

select bmi.*,
       (select bmi2.bmi
        from bmi bmi2
        where bmi2.id = bmi.id and bmi2.bmi is not null
        order by bmi2.date
        limit 1
       ) as first_bmi
from bmi;

Upvotes: 0

GMB
GMB

Reputation: 222582

You could join your table with a subquery that recovers the first non-null BMI, date-wise:

select
    t.*,
    x.bmi first_non_null_bmi
from mytable t
cross join (select bmi from mytable where bmi is not null order by date limit 1) x

Upvotes: 1

ferye21
ferye21

Reputation: 23

I think you can do something like this.. maybe will work.. or gives you any idea

   THEN (select BIM = row_number() over (partition by BIM order by DATE desc/asc) 
      from Products)
ELSE..

Upvotes: 0

Related Questions