How to Fill Column Null Values from Previous Non-null Value



 
 
IF OBJECT_ID(N'dbo.Test', N'U') IS NOT NULL DROP TABLE dbo.test;
GO
CREATE TABLE dbo.test (  id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,  col1 INT NULL);
 

INSERT INTO dbo.Test(id, col1) VALUES
  ( 2, NULL),
  ( 3,   10),
  ( 5,   -1),
  ( 7, NULL),
  (11, NULL),
  (13,  -12),
  (17, NULL),
  (19, NULL),
  (23, 1759);


 
SELECT id, col1,
  CAST(SUBSTRING(MAX( CAST(id AS BINARY(4)) + CAST(col1 AS BINARY(4)) ) OVER( ORDER BY id ROWS UNBOUNDED PRECEDING ), 5, 4) AS INT) AS lastval
FROM dbo.test;


drop table test


--By Itzik Ben-Gan
--http://sqlmag.com/t-sql/last-non-null-puzzle



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s