title: First and last value aggregate functions in postgresql that work correctly with NULL values
body: <p>I know there are aggregate functions for getting last and first value of rows in <a href="https://wiki.postgresql.org/wiki/First/last_%28aggregate%29" rel="nofollow noreferrer">postgresql</a></p>
  
  <p>My problem is, that they do not work as i need. And i could use the help of one postgresql wizard. I'm using postgresql 9.2 - in case the version makes offering solution easyer.</p>
  
  <p>Query</p>
  
  <pre><code>select v.id, v.active, v.reg_no, p.install_date, p.remove_date 
  from vehicle v 
      left join period p on (v.id = p.car_id) 
  where v.id = 1 
  order by v.id, p.install_date asc
  </code></pre>
  
  <p>Returns 6 rows:</p>
  
  <pre><code>id, active, reg_no, install_date, remove_date
  1, TRUE, something, 2008-08-02 11:13:39, 2009-02-09 10:32:32
  ....
  1, TRUE, something, 2010-08-15 21:16:40, 2012-08-25 07:44:30
  1, TRUE, something, 2012-09-10 17:05:12, NULL
  </code></pre>
  
  <p>But when i use aggregating query:</p>
  
  <pre><code>select max(id) as id, last(active) as active, first(install_date) as install_date, last(remove_date) as remove_date 
  from (
      select v.id, v.active, v.reg_no, p.install_date, p.remove_date 
      from vehicle v 
        left join period p on (v.id = p.car_id) 
      where v.id = 1 
      order by v.id, p.install_date asc
  ) as bar 
  group by id
  </code></pre>
  
  <p>Then i get</p>
  
  <pre><code>id, active, install_date, remove_date
  1, TRUE, 2008-08-02 11:13:39, 2012-08-25 07:44:30
  </code></pre>
  
  <p>not</p>
  
  <pre><code>id, active, install_date, remove_date
  1, TRUE, 2008-08-02 11:13:39, NULL
  </code></pre>
  
  <p>as i expected</p>
  
  <p>Is it possible to change the aggregate functions somehow to yield NULL if the value of last row is null, not last existing value?</p>
  
  <p><strong>EDIT1</strong></p>
  
  <p><a href="https://stackoverflow.com/users/1744834/roman-pekar">Roman Pekar</a> offered <a href="https://stackoverflow.com/questions/20345859/first-and-last-value-aggregate-functions-in-postgresql-that-work-correctly-with/20346066#20346066">alternative solution</a> to my problem, but that does not fit my needs. The reason is - i simplified the original query. But the query i run is more complex. I realise that there might be alternative solutions to my problem - this why is update the post to include the original, more complex, query. Which is:</p>
  
  <pre><code>select partner_id, sum(active) as active, sum(installed) as installed, sum(removed) as removed 
  from (
      select 
      pc.partner_id as partner_id, 
      v.id, 
      CASE WHEN v.active = TRUE THEN 1 ELSE 0 END as active, 
      CASE WHEN first(p.install_date) BETWEEN '2013-12-01' AND '2014-01-01' THEN 1 ELSE 0 END as installed,
      CASE WHEN last(p.remove_date) BETWEEN '2013-12-01' AND '2014-01-01' THEN 1 ELSE 0 END as removed 
      from vehicle v 
          left join period p on (v.id = p.car_id) 
          left join partner_clients pc on (pc.account_id = v.client_id) 
      group by pc.partner_id, v.id, v.active
  ) as foo group by partner_id
  </code></pre>
  
  <p>As you can see, i actually need to get first and last value of several vehicles not one and in the end aggregate the counts of those vehicles by the owners of those vehicles.</p>
  
  <p><strong>/EDIT1</strong></p>