StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can try a CTE (Common Table Expression) using <code>ROW_NUMBER</code>:</p> <pre><code>;WITH Readings AS ( SELECT mdc.id, mr.EndTime, ROW_NUMBER() OVER(PARTIION BY mdc.id ORDER BY mr.EndTime DESC) AS 'RowID' FROM dbo.MeterDataConfiguration mdc INNER JOIN dbo.MeterRecording mr ON mr.MeterDataConfigurationId = mdc.Id ) SELECT ID, EndTime, RowID FROM Readings WHERE RowID = 1 </code></pre> <p>This creates "partitions" of data, one for each <code>mdc.id</code>, and numbers them sequentially, descending on <code>mr.EndTime</code>, so for each partition, you get the most recent reading as the <code>RowID = 1</code> row.</p> <p>Of course, to get decent performance, you need appropriate indices on:</p> <ul> <li><code>mr.MeterDataConfigurationId</code> since it's a foreign key into MeterDataConfiguration, right?</li> <li><code>mr.EndTime</code> since you do an <code>ORDER BY</code> on it</li> <li><code>mdc.Id</code> which I assume is a primary key, so it's indexed already</li> </ul> <p><strong>Update:</strong> sorry, I missed this tidbit:</p> <blockquote> <p>I have a clustered index that covers the EndTime and the MeterDataConfigurationId columns in the MeterRecordings table.</p> </blockquote> <p>Quite honestly : I would toss that. Don't you have some other unique ID on the <code>MeterRecordings</code> table that would be suitable as a clustering index? An INT IDENTITY ID or something??</p> <p>If you have a <strong>compound</strong> index on <code>(EndTime, MeterDataConfigurationId)</code>, this won't be able to be used for both purposes - ordering on <code>EndTime</code>, and joining on <code>MeterDataConfigurationId</code> - one of them will not be doable - pity!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload