<p>Given the following table, how does one calculate the hourly mode, or value with the highest frequency by hour?</p>

<pre><code>CREATE TABLE Values
(
    ValueID int NOT NULL,
    Value int NOT NULL,
    LogTime datetime NOT NULL
)
</code></pre>

<p>So far, I've come up with the following query.</p>

<pre><code>SELECT count(*) AS Frequency, 
DatePart(yy, LogTime) as [Year], 
DatePart(mm, LogTime) as [Month],
DatePart(dd, LogTime) as [Day], 
DatePart(hh, LogTime) as [Hour]
FROM Values
GROUP BY 
Value,
DatePart(yy, LogTime), 
DatePart(mm, LogTime),
DatePart(dd, LogTime), 
DatePart(hh, LogTime)
</code></pre>

<p>However, this yields the frequency of each distinct value by hour. How do I add a constraint to only return the value with the maximum frequency by hour?</p>

<p>Thanks</p>

Nest the aggregates...
```
```
SELECT
    MAX(Frequency) AS [Mode],
    [Year],[Month],[Day],[Hour]
FROM
    (SELECT
         COUNT(*) AS Frequency, 
         DatePart(yy, LogTime) as [Year], 
         DatePart(mm, LogTime) as [Month], 
         DatePart(dd, LogTime) as [Day], 
         DatePart(hh, LogTime) as [Hour]
    FROM 
         Values 
    GROUP BY 
         Value, 
         DatePart(yy, LogTime), 
         DatePart(mm, LogTime), 
         DatePart(dd, LogTime), 
         DatePart(hh, LogTime)
    ) foo
GROUP By
    [Year],[Month],[Day],[Hour]
```
```