hmd hmd - 5 months ago 16
SQL Question

Aggregate on datetime in sql with group by

The following are available aggregate functions for SQL

AVG() - Returns the average value
COUNT() - Returns the number of rows
FIRST() - Returns the first value
LAST() - Returns the last value
MAX() - Returns the largest value
MIN() - Returns the smallest value
SUM() - Returns the sum


I need to apply aggregate function on datetime field? It is not listed there. Max(), Min() will not work. What I would need is either


  • return the latest date

  • return the earliest date



Is it possible. Can I implement it somehow?

Answer

min() and max() work fine with dates

you can also do

latest

select top 1 *
from Table
order by SomeDate desc

earliest

select top 1 *
from Table
order by SomeDate 

BTW SQL Server does not have the first() and last() functions