title: Using GROUP BY with FIRST_VALUE and LAST_VALUE
body: <p>I'm working with some data that is currently stored in 1 minute intervals that looks like this:</p>
  
  <pre><code>CREATE TABLE #MinuteData
      (
        [Id] INT ,
        [MinuteBar] DATETIME ,
        [Open] NUMERIC(12, 6) ,
        [High] NUMERIC(12, 6) ,
        [Low] NUMERIC(12, 6) ,
        [Close] NUMERIC(12, 6)
      );
  
  INSERT  INTO #MinuteData
          ( [Id], [MinuteBar], [Open], [High], [Low], [Close] )
  VALUES  ( 1, '2015-01-01 17:00:00', 1.557870, 1.557880, 1.557870, 1.557880 ),
          ( 2, '2015-01-01 17:01:00', 1.557900, 1.557900, 1.557880, 1.557880 ),
          ( 3, '2015-01-01 17:02:00', 1.557960, 1.558070, 1.557960, 1.558040 ),
          ( 4, '2015-01-01 17:03:00', 1.558080, 1.558100, 1.558040, 1.558050 ),
          ( 5, '2015-01-01 17:04:00', 1.558050, 1.558100, 1.558020, 1.558030 ),
          ( 6, '2015-01-01 17:05:00', 1.558580, 1.558710, 1.557870, 1.557950 ),
          ( 7, '2015-01-01 17:06:00', 1.557910, 1.558120, 1.557910, 1.557990 ),
          ( 8, '2015-01-01 17:07:00', 1.557940, 1.558250, 1.557940, 1.558170 ),
          ( 9, '2015-01-01 17:08:00', 1.558140, 1.558200, 1.558080, 1.558120 ),
          ( 10, '2015-01-01 17:09:00', 1.558110, 1.558140, 1.557970, 1.557970 );
  
  SELECT  *
  FROM    #MinuteData;
  
  DROP TABLE #MinuteData;
  </code></pre>
  
  <p>The values track currency exchange rates, so for each minute interval (bar), there is the <code>Open</code> price as the minute started and a <code>Close</code> price for the minute end. The <code>High</code> and <code>Low</code> values represent the highest and lowest rate during each individual minute.</p>
  
  <p><strong>Desired Output</strong></p>
  
  <p>I'm looking to reformat this data in to 5 minute intervals to produce the following output:</p>
  
  <pre><code>MinuteBar                Open       Close       Low         High
  2015-01-01 17:00:00.000  1.557870   1.558030    1.557870    1.558100
  2015-01-01 17:05:00.000  1.558580   1.557970    1.557870    1.558710
  </code></pre>
  
  <p>This takes the <code>Open</code> value from the first minute of the 5, the <code>Close</code> value from the last minute of the 5. The <code>High</code> and <code>Low</code> values represent the highest <code>high</code> and lowest <code>low</code> rates across the 5 minute period.</p>
  
  <p><strong>Current Solution</strong></p>
  
  <p>I have a solution that does this (below), but it feels inelegant as it relies on <code>id</code> values and self joins. Also, I intend to run it on much larger datasets so I was looking to do it in a more efficient manner if possible:</p>
  
  <pre><code>-- Create a column to allow grouping in 5 minute Intervals
  SELECT  Id, MinuteBar, [Open], High, Low, [Close], 
  DATEDIFF(MINUTE, '2015-01-01T00:00:00', MinuteBar)/5 AS Interval
  INTO    #5MinuteData
  FROM    #MinuteData
  ORDER BY minutebar
  
  -- Group by inteval and aggregate prior to self join
  SELECT  Interval ,
          MIN(MinuteBar) AS MinuteBar ,
          MIN(Id) AS OpenId ,
          MAX(Id) AS CloseId ,
          MIN(Low) AS Low ,
          MAX(High) AS High
  INTO    #DataMinMax
  FROM    #5MinuteData
  GROUP BY Interval;
  
  -- Self join to get the Open and Close values
  SELECT  t1.Interval ,
          t1.MinuteBar ,
          tOpen.[Open] ,
          tClose.[Close] ,
          t1.Low ,
          t1.High
  FROM    #DataMinMax t1
          INNER JOIN #5MinuteData tOpen ON tOpen.Id = OpenId
          INNER JOIN #5MinuteData tClose ON tClose.Id = CloseId;
  
  DROP TABLE #DataMinMax
  DROP TABLE #5MinuteData
  </code></pre>
  
  <p><strong>Rework Attempt</strong></p>
  
  <p>Instead of the above queries, I've been looking at using <a href="https://msdn.microsoft.com/en-us/library/hh213018.aspx" rel="noreferrer">FIRST_VALUE</a> and <a href="https://msdn.microsoft.com/en-us/library/hh231517.aspx" rel="noreferrer">LAST_VALUE</a>, as it seems to be what I'm after, but I can't quite get it working with the grouping that I'm doing. There might be a better solution than what I'm trying to do, so I'm open to suggestions. Currently I'm trying to do this:</p>
  
  <pre><code>SELECT  MIN(MinuteBar) MinuteBar5 ,
          FIRST_VALUE([Open]) OVER (ORDER BY MinuteBar) AS Opening,
          MAX(High) AS High ,
          MIN(Low) AS Low ,
          LAST_VALUE([Close]) OVER (ORDER BY MinuteBar) AS Closing ,
          DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5 AS Interval
  FROM    #MinuteData
  GROUP BY DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5
  </code></pre>
  
  <p>This gives me the below error, which is related to the <code>FIRST_VALUE</code> and <code>LAST_VALUE</code> as the query runs if I remove those lines:</p>
  
  <blockquote>
    <p>Column '#MinuteData.MinuteBar' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  </blockquote>