Query to collect the recent TimeSampled row using inner joins in MS SQL

Question

I have written below inner join query to get the data from my database tables. But output of this data is coming up with almost 140+ lines. Output is below. I need data in only one row with the most recent value by "TimeSampled"

select DisplayName, ObjectName, CounterName, InstanceName, SampleValue, max(TimeSampled) as TimeSampled
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where ObjectName = 'Processor'  AND TimeSampled >= DATEADD(MINUTE,-5,GETDATE())
AND DisplayName Like 'BLRW1ETST01.jcp.com'
AND CounterName = '% Processor Time'
GROUP BY DisplayName, ObjectName, CounterName, InstanceName, SampleValue
ORDER BY  TimeSampled desc

How I can retrive results with only last Time Sampled row with respect to DisplayName. I have multiple servers (DisplayName) like this to retrieve the data.

Thanks, Ramu Chittiprolu

Answer 1

One way is to use the ROW_NUMBER window function to order each row grouped by DisplayName (or better, the underlying primary key, if different and DisplayName isn't indexed), then pick out the latest using the result:

SELECT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
FROM (
    SELECT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled,
        OrderNum = ROW_NUMBER() OVER (
            PARTITION BY DisplayName
            ORDER BY TimeSampled DESC)
    FROM PerformanceDataAllView pdv
    INNER JOIN PerformanceCounterView pcv
    ON pdv.performancesourceinternalid = pcv.performancesourceinternalid
    INNER JOIN BaseManagedEntity bme
    ON pcv.ManagedEntityId = bme.BaseManagedEntityId
    WHERE ObjectName = 'Processor'
        AND TimeSampled >= DATEADD(MINUTE,-5,GETDATE())
        AND DisplayName LIKE 'BLRW1ETST01.jcp.com'
        AND CounterName = '% Processor Time'
) t
WHERE OrderNum = 1;

That said, there are a few oddities with your query

• It's better to qualify column references with the table alias to avoid mishaps if same-named columns get added to different tables
• You use LIKE for filtering on DisplayName, yet specify a simple string rather than a pattern
• The filter on TimeSampled means, if the most recent entry for a DisplayName is more than five minutes ago, it won't be returned despite being the most recent entry, which goes against how you described the requirements
• Be careful on thinking NOLOCK is a magic bullet (https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere)