SQL: Efficiently finding minimum data points within separate subranges for each column
<p>I have a SQL query (targeting SQL Server, sadly) which looks something like this:</p>

<pre><code>SELECT
    DATETIMEFROMPARTS(YEAR(ts), MONTH(ts), DAY(ts), 0, 0, 0, 0) AS day,
    AVG(CASE WHEN DATEPART(HOUR, ts) = 0 THEN val ELSE NULL END) AS hr0,
    AVG(CASE WHEN DATEPART(HOUR, ts) = 1 THEN val ELSE NULL END) AS hr1,
    -- ...etc for other hours...
FROM 
    sample_data
GROUP BY 
    DATETIMEFROMPARTS(YEAR(ts), MONTH(ts), DAY(ts), 0, 0, 0, 0)
</code></pre>

<p>This works perfectly well, calculating (for each day) an average value for each hour.</p>

<p>My requirements are changing, however: I'm now asked for only the <em>first</em> sample for each hour. Thus, <code>hr0</code> should reflect only a single <code>value</code> for the smallest <code>ts</code> satisfying <code>DATEPART(HOUR, ts) = 0</code> (and, of course, still being within the same day), or a NULL if none exist.</p>

<p>The obvious approach that comes to mind is using a subquery for each hour, but this strikes me as a significant decrease in runtime efficiency (and my implementation attempts are not only slow but also <em>ugly</em>). Is there a better alternative I'm not considering?</p>