title: How to select last non-NULL columns in a set
body: <p>I am creating a table that has a series of revisions for each logical row, and uses the NULL value to indicate the a particular cell has the same value as the last revision.  For example:</p>
  
  <pre><code>id | logical_id | revision | x | y | z | other
  ---+------------+----------+---+---+---+--------
   1 |     1      |     1    | 1 | 2 | 5 | blue
   2 |     2      |     1    | 3 | 5 | 9 | red
   3 |     1      |     2    | 9 |   |   |
   4 |     2      |     2    |   | 7 |   | orange
   5 |     1      |     3    |   | 6 |   |
   6       3      |     1    | 0 | 0 | 0 | white
  </code></pre>
  
  <p>I need a query that takes each logical_id and builds a row containing the latest entry for each field after the revision ("regular fields"), in this case <code>x</code>, <code>y</code>, <code>z</code>, and <code>other</code>.  The number of such fields is arbitrary although it is perfectly acceptable that the query would need to be modified based on what these fields actually are (and I expect it).</p>
  
  <p>So for the above table, the output would be:</p>
  
  <pre><code>logical_id | x | y | z | other
  -----------+---+---+---+--------
      1      | 9 | 6 | 5 | blue
      2      | 3 | 7 | 9 | orange
      3      | 0 | 0 | 0 | white
  </code></pre>
  
  <p>I found this question, "<a href="https://stackoverflow.com/questions/8977398/database-select-last-non-null-entries">Database: Select last non-null entries</a>", which seems to be almost exactly the same as my question but the question is with regards to Postgresql, and the solutions given are either using language constructs not available in SQLite (FIRST_VALUE, PARTITION BY?), seem to only answer part of the problem (for instance, only finding the result for a single logical id), or when I tried them in SQLite they simply didn't work.</p>
  
  <p>As an added bonus, I would also like to be able to mark a <code>logical_id</code> as being deleted by setting all "regular fields" to NULL, if that is possible.</p>