Using GROUP BY with FIRST_VALUE and LAST_VALUE

by 
SELECT 
    MIN(MinuteBar) AS MinuteBar5,
    Opening,
    MAX(High) AS High,
    MIN(Low) AS Low,
    Closing,
    Interval
FROM 
(
    SELECT FIRST_VALUE([Open]) OVER (PARTITION BY DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5 ORDER BY MinuteBar) AS Opening,
           FIRST_VALUE([Close]) OVER (PARTITION BY DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5 ORDER BY MinuteBar DESC) AS Closing,
           DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5 AS Interval,
           *
    FROM #MinuteData
) AS T
GROUP BY Interval, Opening, Closing

A solution close to your current one. There are two places you did wrong.

  1. FIRST_VALUE AND LAST_VALUE are Analytic Functions, which work on a window or partition, instead of a group. You can run the nested query alone and see its result.

  2. LAST_VALUE is the last value of current window, which is not specified in your query, and a default window is rows from the first row of current partition to current row. You can either use FIRST_VALUE with descending order or specify a window

    LAST_VALUE([Close]) OVER (PARTITION BY DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5 
            ORDER BY MinuteBar 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Closing,

More Related Contents:

  1. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  2. replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)
  3. Function to Calculate Median in SQL Server
  4. Column “invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”
  5. SQL Server: Difference between PARTITION BY and GROUP BY
  6. SQL Server – Remove all non-printable ASCII characters
  7. T-SQL incrementing counter based on change in a column value
  8. Include missing months in Group By query
  9. Count number of records returned by group by
  10. Local and global temporary tables in SQL Server
  11. SQL Server add auto increment primary key to existing table
  12. SQL Server: UPDATE a table by using ORDER BY
  13. EF5: Cannot attach the file ‘{0}’ as database ‘{1}’
  14. SQL Server: How to select all days in a date range even if no data exists for some days
  15. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  16. TSQL: How to convert local time to UTC? (SQL Server 2008)
  17. Transpose rows and columns with no aggregate
  18. How do I pass value to a stored procedure parameter in OLE DB Source component?
  19. Possible to set default schema from connection string?
  20. How does sql server choose values in an update statement where there are multiple options?
  21. WHERE clause better execute before IN and JOIN or after
  22. How to reuse calculated columns avoiding duplicating the sql statement
  23. Commas within CSV Data
  24. nvarchar(max) vs NText
  25. How to use RANK() in SQL Server
  26. Unable to find the requested .Net Framework Data Provider. It may not be installed. – when following mvc3 asp.net tutorial
  27. How to set value to variable using ‘execute’ in t-sql?
  28. Taking the second last row with only one select in SQL Server?
  29. SQL Server Maximum rows that can be inserted in a single insert statment
  30. How to Bulk Insert from XLSX file extension?

Leave a Comment