PostgreSQL: Query that repeats last row result when current row is null

Question

I've been trying to created a query that repeats last row result when current row is null, e.g.:

Here is my data:

A1  |   B2
--------------
1   |   2
2   |   1
3   |   (null)
4   |   (null)
5   |   3

So I want that to be:

A1  |   B2
--------------
1   |   2
2   |   1
3   |   1
4   |   1
5   |   3

When the value is null, I want to fill with the last row result. I tried:

SELECT A1, COALESCE (B2, Last_Value (B2) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) AS B2 FROM

(SELECT 1 AS A1, 2 AS B2
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3, NULL
UNION ALL
SELECT 4, NULL
UNION ALL
SELECT 5, 3) TAB

But the result is:

A1  |   B2
--------------
1   |   2
2   |   1
3   |   1
4   |   (null)
5   |   3

Anyone knows how to do this?

Thanks,

Gordon Linoff · Accepted Answer · 2015-12-17 12:02:09Z

4

Alas, Postgres doesn't support the ignore null option for lag(). But you can still do this.

The idea is to count the number of rows with a value in B2 prior to each row. I'm assuming that A1 gives the ordering of the values. Then you can use max() over this range to assign the value.

select t.a1,
       coalesce(t.b1, max(b2) over (partition by grp)) as b2
from (select t.*,
             sum(case when b2 is null then 0 else 1 end) over (order by a1) as grp
      from tab t
     ) t;

I don't think the coalesce() is necessary, because the max() should be calculating the right value even when b2 is not null. But, I think it makes the code clearer.

EDIT:

The above query can be slightly simplified to:

select t.a1,
       coalesce(t.b1, max(b2) over (partition by grp)) as b2
from (select t.*,
             count(b2) over (order by a1) as grp
      from tab t
     ) t;

edited Dec 17, 2015 at 12:02

answered Mar 25, 2014 at 21:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Query that repeats last row result when current row is null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related