How to fill null with previous row value?

Asked

Viewed 117 times

2

Looking at the following table, I want to fill the null value with the value of the previous row, which is best to do with mysql?

tabela

mysql query

  • Related: http://stackoverflow.com/questions/26977267/fill-null-values-with-last-non-null-amount-oracle-sql

1 answer

2

Whereas the rows are ordered by the header column 1, and this column is numerical and sequential (no holes in the numbering):

select  cabecalho1, cabecalho2, 
        novoresultado = isnull(cabecalho2,(select cabecalho2 from yourTable where cabecalho1= t.cabecalho1-1))
from yourTable t

--update
update yourTable
set novoresultado = isnull(cabecalho2,(select cabecalho2 from yourTable t where t.cabecalho1 = yourTable.cabecalho1-1))

Whereas the rows are ordered by the column header 1, and this column is numerical, but with the possibility of missing numbers in the sequence:

select  cabecalho1, cabecalho2, 
        novoresultado = isnull(cabecalho2,(select top 1 cabecalho2 from yourTable where cabecalho1 < t.cabecalho1 order by cabecalho1 desc))
from yourTable t

--update
update yourTable
set novoresultado = isnull(cabecalho2,(select top 1 cabecalho2 from yourTable t where t.cabecalho1 < yourTable.cabecalho1 order by cabecalho1 desc))

Browser other questions tagged mysql query

You are not signed in. Login or sign up in order to post.