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?

CodePudding user response：

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?

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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;