Skip to content
Advertisement

SQL group by date of DateTime and select first and last value

I am grouping by the date part of a DateTime column using the CONVERT function selecting the MAX and MIN price value for that date. I would like to include the first and last price values for the group based on the full DateTime value.

The current query looks like this:

SELECT CONVERT(date, dateColumn) [dateColumn], MAX(price) [maxColumn], MIN(price) [minColumn] 
FROM table
GROUP BY CONVERT(date, dateColumn)

Is it possible to include the first and last price in this group with the smallest/largest full DateTime without using a subquery?

Advertisement

Answer

It is possible without a subquery, but at the expense of performance:

SELECT DISTINCT CONVERT(date, dateColumn) as dateColumn,
       MAX(price) OVER (PARTITION BY CONVERT(date, dateColumn)) as max_price,
       MIN(price) OVER (PARTITION BY CONVERT(date, dateColumn)) as min_price,
       FIRST_VALUE(price) OVER (PARTITION BY CONVERT(date, dateColumn) ORDER BY dateColumn ASC) as first_price,
       FIRST_VALUE(price) OVER (PARTITION BY CONVERT(date, dateColumn) ORDER BY dateColumn DESC) as last_price
FROM table;
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement