repeat value of a row when other row is null

Question

I have a table like this:

 id | nu_cns | nu_cpf | co_dim_tempo | sifilis | hiv
908 |   null |    347 |            1 |       y |   n
908 |    708 |   null |            2 |       y |   y
908 |    708 |   null |            3 |       y |   y

I need to repeat the fields' values of nu_cns and nu_cpf when these fields are null, for example:

 id | nu_cns | nu_cpf | co_dim_tempo | sifilis | hiv
908 |   *708 |    347 |            1 |       y |   n
908 |    708 |   *347 |            2 |       y |   y
908 |    708 |   *347 |            3 |       y |   y

Could you have different sets of values in nu_cns and nu_cpf for a given id ? — Rajat
– Rajat, Commented Mar 9, 2022 at 16:05
@PhilCoulson sometimes yes, but, if the result list only one, is ok. — Italo Rodrigo
– Italo Rodrigo, Commented Mar 9, 2022 at 16:08
So if you can have multiple values, how do you decide which value replaces the nulls? — Rajat
– Rajat, Commented Mar 9, 2022 at 16:16
@PhilCoulson any value can replace the null value. is very rare more than one value — Italo Rodrigo
– Italo Rodrigo, Commented Mar 9, 2022 at 16:17

Italo Rodrigo · Accepted Answer · 2022-03-14 21:16:33Z

1

You can use last_value with frame clause rows between unbounded preceding and current row:

select id, first_value(nu_cns) 
  over (partition by id order by nu_cns rows between unbounded preceding and current row)
  from my_table

edited Mar 14, 2022 at 21:16

Italo Rodrigo

1,7855 gold badges20 silver badges42 bronze badges

answered Mar 9, 2022 at 15:45

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

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

4 Comments

Italo Rodrigo Over a year ago

syntax error at or near "preceeding" LINE 1: ...ver (order by co_dim_tempo rows between unbounded preceeding...

Andronicus Over a year ago

@ItaloRodrigo of course it's a typo, should be preceding, sorry :>

Italo Rodrigo Over a year ago

Works fine like this: select id, first_value(nu_cns) over (partition by id order by nu_cns rows between unbounded preceding and current row) can you edit your answer, please?

Andronicus Over a year ago

@ItaloRodrigo, sure. Note that it would also work if you changed the ordering with desc

Rajat · Accepted Answer · 2022-03-09 16:31:04Z

1

If you're fine with any value, just do..

select *, 
       max(nu_cns) over (partition by id),
       max(nu_cpf) over (partition by id)  
from t;

answered Mar 9, 2022 at 16:31

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Collectives™ on Stack Overflow

repeat value of a row when other row is null

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related