Replace NULL with the latest NOT NULL value in a column

This is a very commonly asked interview question. Replace NULL value with the latest NOT NULL value in a column.

ID Name
1 Prasad Sahoo
2 NULL
3 NULL
4 NULL
5 Ashish Mishal
6 NULL
7 Narotam Gediya

The above is how the original table looks like, we need to write down a query to replace the NULL values with the latest NOT NULL value from the column “Name”. In short we need a result set like below table.

ID Name
1 Prasad Sahoo
2 Ashish Mishal
3 Ashish Mishal
4 Ashish Mishal
5 Ashish Mishal
6 Narotam Gediya
7 Narotam Gediya

If you know any other tricks to answer the same question, then please leave your answer in below comment section. Highly appreciate your time.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

2 thoughts on “Replace NULL with the latest NOT NULL value in a column

  • November 27, 2014 at 4:53 pm
    Permalink

    If identity column is not there then what would happened??

    Reply
    • November 28, 2014 at 11:55 am
      Permalink

      I appreciate the question Jaysukh.. I think this can be done using a recursive CTE.. Will try and post the query soon.

      Reply

Leave a Reply

%d bloggers like this: