title: Database: Select last non-null entries
body: <p>Here's a question I've been racking my brain over. Let's say I have a table that has a series of timestamps and a part number as the primary key. The table stores incremental changes, meaning that for every timestamp, if a field changes, that change is recorded. If the field doesn't change, then for the new timestamp it is NULL.
  Here's the basic idea.</p>
  
  <pre><code> part | timestamp | x-pos | y-pos | status
  ------+-----------+-------+-------+--------
   a5   |       151 |     5 |    15 |      g
   a5   |       153 |  NULL |    17 |   NULL
  </code></pre>
  
  <p><code>(part, timestamp)</code> is the primary key. The <code>NULL</code>s in the second record indicate values that are unchanged since the first record.</p>
  
  <p>What I want to be able to do is select the most recent values for each field grouped by the part. For example, given the above entries, the results will be 153,5,17,g for part a5.</p>
  
  <p>As of now, I have this hacked together query.</p>
  
  <pre><code>    ((SELECT x-pos FROM part_changes WHERE x-pos IS NOT NULL
      ORDER BY timestamp DESC
      LIMIT 1)
  
      UNION
  
      (SELECT y-pos FROM part_changesWHERE y-pos IS NOT NULL
      ORDER BY timestamp DESC
      LIMIT 1)
  
      UNION
  
      (SELECT status FROM part_changes WHERE status IS NOT NULL
      ORDER BY timestamp DESC
      LIMIT 1))
  </code></pre>
  
  <p>But this returns a single column, meaning that I can use a group-by for organizing.</p>
  
  <p>There's got to be a more elegant way of doing thing, such as using COALESCE or IS NULL in a creative way. But I'm stuck and can't figure it out. Anybody got an idea? </p>
  
  <p>And no, I can't change the database structure.</p>
  
  <p>EDIT: ruakh has the right idea. The only problem now is grouping by part. I can't seem to get around the <code>LIMIT 1</code> for grouping by multiple parts. Any ideas? </p>
  
  <p>mdahlman, I'm not too familiar with analytic functions in postgresql. So, if that solution would be easier than a complex query, then by all means post your idea.</p>
  
  <p>EDIT 2: Thank you all for the help. I think I've got a good enough grasp of what I need to do.</p>