How do I get a maximium daily value of a numerical field over a year in SQL

Question

How do I get a maximium daily value of a numerical field over a year in MS-SQL

Answer 1

This would query the daily maximum of value over 2008:

select 
    datepart(dayofyear,datecolumn)
,   max(value)
from yourtable
where '2008-01-01' <= datecolumn and datecolumn < '2009-01-01'
group by datepart(dayofyear,datecolumn)

Or the daily maximum over each year:

select 
    datepart(year,datecolumn), 
,   datepart(dayofyear,datecolumn)
,   max(value)
from yourtable
group by datepart(year,datecolumn), datepart(dayofyear,datecolumn)

Or the day(s) with the highest value in a year:

select 
    Year = datepart(year,datecolumn),
,   DayOfYear = datepart(dayofyear,datecolumn)
,   MaxValue = max(MaxValue)
from yourtable
inner join (
    select 
        Year = datepart(year,datecolumn), 
    ,   MaxValue = max(value)
    from yourtable
    group by datepart(year,datecolumn)
) sub on 
    sub.Year = yourtable.datepart(year,datecolumn)
    and sub.MaxValue = yourtable.value
group by 
    datepart(year,datecolumn),
    datepart(dayofyear,datecolumn)

Answer 2

Something like

SELECT dateadd(dd,0, datediff(dd,0,datetime)) as day, MAX(value) 
FROM table GROUP BY dateadd(dd,0, datediff(dd,0,datetime)) WHERE 
datetime < '2009-01-01' AND datetime > '2007-12-31'

Assuming datetime is your date column, dateadd(dd,0, datediff(dd,0,datetime)) will extract only the date part, and then you can group by that value to get a maximum daily value. There might be a prettier way to get only the date part though.

You can also use the between construct to avoid the less than and greater than.

Answer 3

You didn't mention which RDBMS or SQL dialect you're using. The following will work with T-SQL (MS SQL Server). It may require some modifications for other dialects since date functions tend to change a lot between them.

SELECT
     DATEPART(dy,  my_date),
     MAX(my_number)
FROM
     My_Table
WHERE
     my_date >= '2008-01-01' AND
     my_date < '2009-01-01'
GROUP BY
     DATEPART(dy, my_date)

The DAY function could be any function or combination of functions which gives you the days in the format that you're looking to get.

Also, if there are days with no rows at all then they will not be returned. If you need those days as well with a NULL or the highest value from the previous day then the query would need to be altered a bit.

Answer 4

Group on the date, use the max delegate to get the highest value for each date, sort on the value, and get the first record.

Example:

select top 1 theDate, max(theValue)
from TheTable
group by theDate
order by max(theValue) desc

(The date field needs to only contain a date for this grouping to work, i.e. the time component has to be zero.)

If you need to limit the query for a specific year, use a starting and ending date in a where claues:

select top 1 theDate, max(theValue)
from TheTable
where theDate between '2008-01-01' and '2008-12-13'
group by theDate
order by max(theValue) desc