How to select the last value which is not null?

Question

I have the following table:

id a b 
1  1 kate
1  4 null
1  3 paul
1  3 paul
1  2 lola
2  1 kim
2  9 null
2  2 null

In result it should be this:

1 3 paul
2 1 kim

I want to get the last a where b is not null. Something like:

select b
from (select,b 
row_num() over (partition by id order by a desc) as num) as f
where num = 1

But this way I get a null value, because to the last a = 4 corresponds to b IS NULL. Maybe there is a way to rewrite ffill method from pandas?

I assume the desired result should read `kim` instead of `kate`? — Erwin Brandstetter, Oct 18 '21 at 21:32

Erwin Brandstetter · Answer 1 · 2021-10-18T21:36:53.170

0

Assuming:

a is defined NOT NULL.
You want the row with the greatest a where b IS NOT NULL - per id.

SELECT DISTINCT ON (id) *
FROM   tbl
WHERE  b IS NOT NULL
ORDER  BY id, a DESC;

db<>fiddle here

Detailed explanation:

Select first row in each GROUP BY group?

edited Oct 18 '21 at 21:36

answered Oct 18 '21 at 21:31

Erwin Brandstetter

605,456
145
1,078
1,228

score 0 · Answer 2 · answered Oct 18 '21 at 21:45

0

Try:

select id, a, b
from (select id, a, b,
    row_num() over (partition by id order by a desc nulls last) as num
    from unnamedTable) t
where num = 1

Or, if that isn't right, try it with nulls first. I can never remember which way it works with desc.

answered Oct 18 '21 at 21:45

Bacon Bits

30,782
5
59
66

I had the same thought. Unfortunately the nulls are in column b. – shawnt00 Oct 18 '21 at 22:03
Oh, you're right. In that case, use shawnt00's [answer](https://stackoverflow.com/a/69622920/696808). – Bacon Bits Oct 19 '21 at 00:06

shawnt00 · Answer 3 · 2021-10-18T22:18:02.450

0

If you aren't guaranteed to have at least one non-null per id then you'll want to move nulls to the bottom of the list rather than filtering those rows out entirely.

select id, a, b
from (
    select id, a, b, 
        row_number() over (
            partition by id
            order by case when b is not null then 0 else 1 end, a desc
        ) as num
) as f
where num = 1

edited Oct 18 '21 at 22:18

answered Oct 18 '21 at 21:49

shawnt00

16,443
3
17
22

score 0 · Answer 4 · answered Oct 18 '21 at 23:31

You can wrap this around a cte and join it back to the main table if you wish to keep the original columns as is, but looking at your expected output and logic, this should do it. Having said that, row_number() based approach might be a tad faster.

select distinct 
       id, 
       max(a) over (partition by id) as a, 
       first_value(b) over (partition by id order by a desc) as b
from tbl
where b is not null;

How to select the last value which is not null?

4 Answers4