title: T-SQL: Best way to replace NULL with most recent non-null value?
body: <p>Assume I have this table:</p>
  
  <pre><code>+----+-------+
  | id | value |
  +----+-------+
  |  1 |     5 |
  |  2 |     4 |
  |  3 |     1 |
  |  4 |  NULL |
  |  5 |  NULL |
  |  6 |    14 |
  |  7 |  NULL |
  |  8 |     0 |
  |  9 |     3 |
  | 10 |  NULL |
  +----+-------+
  </code></pre>
  
  <p>I want to write a query that will replace any <code>NULL</code> value with the <em>last value in the table that was not null</em> in that column.</p>
  
  <p>I want this result:</p>
  
  <pre><code>+----+-------+
  | id | value |
  +----+-------+
  |  1 |     5 |
  |  2 |     4 |
  |  3 |     1 |
  |  4 |     1 |
  |  5 |     1 |
  |  6 |    14 |
  |  7 |    14 |
  |  8 |     0 |
  |  9 |     3 |
  | 10 |     3 |
  +----+-------+
  </code></pre>
  
  <p>If no previous value existed, then NULL is OK. Ideally, this should be able to work even with an <code>ORDER BY</code>. So for example, if I <code>ORDER BY [id] DESC</code>:</p>
  
  <pre><code>+----+-------+
  | id | value |
  +----+-------+
  | 10 |  NULL |
  |  9 |     3 |
  |  8 |     0 |
  |  7 |     0 |
  |  6 |    14 |
  |  5 |    14 |
  |  4 |    14 |
  |  3 |     1 |
  |  2 |     4 |
  |  1 |     5 |
  +----+-------+
  </code></pre>
  
  <p>Or even better if I <code>ORDER BY [value] DESC</code>:</p>
  
  <pre><code>+----+-------+
  | id | value |
  +----+-------+
  |  6 |    14 |
  |  1 |     5 |
  |  2 |     4 |
  |  9 |     3 |
  |  3 |     1 |
  |  8 |     0 |
  |  4 |     0 |
  |  5 |     0 |
  |  7 |     0 |
  | 10 |     0 |
  +----+-------+
  </code></pre>
  
  <p>I <em>think</em> this might involve some kind of analytic function - somehow partitioning over the value column - but I'm not sure where to look. </p>