SQL Query Replace Null Value in a Row with a Value from the Previous Known Value

Function to REPLACE* last previous known value for NULL

Assuming the number you have is always increasing, you can use MAX aggregate over a window:

SELECT dt
     , country
     , cnt
     , MAX(cnt) OVER (PARTITION BY country ORDER BY dt)
  FROM #data

If the number may decrease, the query becomes a little bit more complex as we need to mark the rows that have nulls as belonging to the same group as the last one without a null first:

SELECT dt
     , country
     , cnt
     , SUM(cnt) OVER (PARTITION BY country, partition)
FROM (
    SELECT country
         , dt
         , cnt
         , SUM(CASE WHEN cnt IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY country ORDER BY dt) AS partition
    FROM #data
) AS d
ORDER BY dt

Here's a working demo on dbfiddle, it returns the same data with ever increasing amount, but if you change the number for 08-17 to be lower than that of 08-16, you'll see MAX(...) method producing wrong results.

How to replace null value with value from the next row

below query works in SQL Server:

;WITH CTE_Value
AS (
    SELECT R#, Value
    FROM @value AS T
    WHERE Value IS NOT NULL

    UNION ALL

    SELECT t.r#, c.Value
    FROM @value AS t
    INNER JOIN CTE_Value AS c ON t.r# + 1 = c.r#
    WHERE t.Value IS NULL
    )
SELECT *
FROM CTE_Value

UNION ALL

SELECT v.*
FROM @value AS v
LEFT JOIN CTE_value AS c ON v.r# = c.r#
WHERE c.r# IS NULL
ORDER BY r#

Oracle SQL - replace null with previous known value

Try last_value analytic function:

SELECT "PROCESSO", "DATAM", "HOURM",
       last_value( "TEMP_T1" ignore nulls )
       OVER (order by "DATAM"
             rows between unbounded preceding and current row
       ) as new_temp
FROM table1

Demo ==> http://sqlfiddle.com/#!4/48207/2

=========== EDIT ===================


If you want to update the table, and there is no primary key (unique identifiers), you can try a solution based on rowid pseudocolumn

==>http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm) :

MERGE INTO table1 t1
USING (
    SELECT rowid rd, "PROCESSO", "DATAM", "HOURM",
           last_value( "TEMP_T1" ignore nulls )
           OVER (order by "DATAM"
                 rows between unbounded preceding and current row
           ) as new_temp
    FROM table1
) x
ON (t1.rowid = x.rd)
WHEN MATCHED THEN UPDATE SET t1."TEMP_T1" = x.new_temp
;

Demo ==> http://sqlfiddle.com/#!4/5a9a61/1



However you must ensure that there is no another process that deletes and inserts rows from/into this table while running the update, because when a row is deleted from the table, Oracle can assigng it's rowid to another, new row.

Fill Null Values with Last Previous Value and add 1 as a continuous integer for every value going forward - Big Query

I'm using similar data as you posted in your question.

with data as (
 SELECT 'Americas_1 '  as id,1 as activity, 'America' as region union all
 SELECT 'Americas_2 '  as id,2 as activity, 'America' as region union all
 SELECT 'Americas_3 '  as id,3 as activity, 'America' as region union all
 SELECT 'Americas_4 '  as id,4 as activity, 'America' as region union all
 SELECT null  as id,null as activity, 'c' as region union all
 SELECT null  as id,null as activity, 'a' as region
)

In the subquery data, I just have the sample data. In the second subquery data2, I added a column number, this column adds the row_number when the activity column is null, if it is not null add a 0. The column new_activity just puts the same numbers when activity is not null.

Here you can see the complete query.

with data as (
 SELECT 'Americas_1 '  as id,1 as activity, 'America' as region union all
 SELECT 'Americas_2 '  as id,2 as activity, 'America' as region union all
 SELECT 'Americas_3 '  as id,3 as activity, 'America' as region union all
 SELECT 'Americas_4 '  as id,4 as activity, 'America' as region union all
 SELECT null  as id,null as activity, 'c' as region union all
 SELECT null  as id,null as activity, 'a' as region
), data2 as (
 
select id,activity, region, 
IF (activity is null,ROW_NUMBER() OVER(ORDER BY activity),0) as number,
IF(activity IS NULL,
last_value(activity ignore nulls) over (order by activity RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ,
activity ) as new_activity
from data
group by id,activity, region
order by activity asc nulls last
)

This query displays these columns ID, activity, region; and in the last column new_Activity, I sum the column number and new_activity from the subquery data2

select id, activity, region, (number+new_activity) as new_Activity from data2
order by activity asc nulls last

This is the output of the query.

SQL Server replace NULL value with last value from previous row

How about?

UPDATE mytable
SET date = date2
FROM mytable o
  CROSS APPLY
    (SELECT MAX(date) date2 FROM mytable i WHERE i.id=o.id group by id) ii
where o.date is null

SQL query- I need to fill the null values to their previous row values. what query to i use?

You can do this in two steps. The idea is to assign a group by counting the non-NULL values in a column. Then you can shmear the values within the group.

Assuming the NULLs match in the two columns (as in the sample data):

select t.*,
       max(dateoftransaction) over (partition by occupancyid, grp) as imputed_dateoftransaction,
       max(rentdue) over (partition by occupancyid, grp) as imputed_rentdue
from (select t.*,
             count(dateoftransaction) over (partition by occupancyid order by calendardate) as grp
      from t
     ) t

---

Related Topics