title: PostgreSQL - how should I use first_value()?
body: <p>This answer to shows how to produce High/Low/Open/Close values from a ticker:<br>
  <a href="https://stackoverflow.com/questions/11533700/postgresql-retrieve-open-high-low-close-with-arbitrary-time-windows/11533808#11533808">Retrieve aggregates for arbitrary time intervals</a></p>
  
  <p>I am trying to implement a solution based on this (PG 9.2), but am having difficulty in getting the correct value for <code>first_value()</code>.</p>
  
  <p>So far, I have tried two queries:</p>
  
  <pre><code>SELECT  
      cstamp,
      price,
      date_trunc('hour',cstamp) AS h,
      floor(EXTRACT(minute FROM cstamp) / 5) AS m5,
      min(price) OVER w,
      max(price) OVER w,
      first_value(price) OVER w,
      last_value(price) OVER w
  FROM trades
  Where date_trunc('hour',cstamp) = timestamp '2013-03-29 09:00:00'
  WINDOW w AS (
      PARTITION BY date_trunc('hour',cstamp), floor(extract(minute FROM cstamp) / 5)
      ORDER BY date_trunc('hour',cstamp) ASC, floor(extract(minute FROM cstamp) / 5) ASC
      )
  ORDER BY cstamp;
  </code></pre>
  
  <p>Here's a piece of the result:</p>
  
  <pre><code>        cstamp         price      h                 m5  min      max      first    last
  "2013-03-29 09:19:14";77.00000;"2013-03-29 09:00:00";3;77.00000;77.00000;77.00000;77.00000
  
  "2013-03-29 09:26:18";77.00000;"2013-03-29 09:00:00";5;77.00000;77.80000;77.80000;77.00000
  "2013-03-29 09:29:41";77.80000;"2013-03-29 09:00:00";5;77.00000;77.80000;77.80000;77.00000
  "2013-03-29 09:29:51";77.00000;"2013-03-29 09:00:00";5;77.00000;77.80000;77.80000;77.00000
  
  "2013-03-29 09:30:04";77.00000;"2013-03-29 09:00:00";6;73.99004;77.80000;73.99004;73.99004
  </code></pre>
  
  <p>As you can see, 77.8 is <em>not</em> what I believe is the correct value for <code>first_value()</code>, which should be 77.0.</p>
  
  <p>I though this might be due to the ambiguous <code>ORDER BY</code> in the <code>WINDOW</code>, so I changed this to </p>
  
  <pre><code>ORDER BY cstamp ASC 
  </code></pre>
  
  <p>but this appears to upset the <code>PARTITION</code> as well:</p>
  
  <pre><code>        cstamp         price      h                 m5  min      max      first    last
  "2013-03-29 09:19:14";77.00000;"2013-03-29 09:00:00";3;77.00000;77.00000;77.00000;77.00000
  
  "2013-03-29 09:26:18";77.00000;"2013-03-29 09:00:00";5;77.00000;77.00000;77.00000;77.00000
  "2013-03-29 09:29:41";77.80000;"2013-03-29 09:00:00";5;77.00000;77.80000;77.00000;77.80000
  "2013-03-29 09:29:51";77.00000;"2013-03-29 09:00:00";5;77.00000;77.80000;77.00000;77.00000
  
  "2013-03-29 09:30:04";77.00000;"2013-03-29 09:00:00";6;77.00000;77.00000;77.00000;77.00000
  </code></pre>
  
  <p>since the values for max and last now <em>vary within the partition</em>.</p>
  
  <p>What am I doing wrong? Could someone help me better to understand the relation between <code>PARTITION</code> and <code>ORDER</code> within a <code>WINDOW</code>?</p>
  
  <hr>
  
  <p>Although I have an answer, here's a trimmed-down pg_dump which will allow anyone to recreate the table. The only thing that's different is the table name.</p>
  
  <pre><code>CREATE TABLE wtest (
      cstamp timestamp without time zone,
      price numeric(10,5)
  );
  
  COPY wtest (cstamp, price) FROM stdin;
  2013-03-29 09:04:54 77.80000
  2013-03-29 09:04:50 76.98000
  2013-03-29 09:29:51 77.00000
  2013-03-29 09:29:41 77.80000
  2013-03-29 09:26:18 77.00000
  2013-03-29 09:19:14 77.00000
  2013-03-29 09:19:10 77.00000
  2013-03-29 09:33:50 76.00000
  2013-03-29 09:33:46 76.10000
  2013-03-29 09:33:15 77.79000
  2013-03-29 09:30:08 77.80000
  2013-03-29 09:30:04 77.00000
  \.
  </code></pre>