title: SQL Windowing function: MAX(date) over Partition BY Clause
body: <p>I have a query as:</p>
  
  <pre><code>select 
      location_id,
      max(created) over (partition by location_id) as "created"
  from 
      device
  group by 
      location_id, created
  order by 
      location_id
  </code></pre>
  
  <p>This results in max date being repeated against each grp.</p>
  
  <blockquote>
    <p>21    2015-01-26T18:25:00.000Z<br>
    22    2015-10-18T13:21:32.000Z<br>
    22    2015-10-18T13:21:32.000Z<br>
    22    2015-10-18T13:21:32.000Z</p>
  </blockquote>
  
  <p>Now if I calculate the difference between the dates:</p>
  
  <pre><code>select 
      location_id,
      date(created),
      max(date(created)) - min(date(created)) over (partition by location_id) as "created"
  from 
      device
  group by 
      location_id, created
  order by 
      location_id
  </code></pre>
  
  <blockquote>
    <p>21    2015-01-26T00:00:00.000Z    0<br>
    22    2015-01-26T00:00:00.000Z    0<br>
    22    2015-03-12T00:00:00.000Z    45<br>
    22    2015-10-18T00:00:00.000Z    265  </p>
  </blockquote>
  
  <p>Why do I get difference between first record and 2nd and then 3rd? Why not just the difference between max-min repeated through the groups?</p>
  
  <p>Hope I am clear with the question.</p>