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
Best Answer
One way is to use the
ROW_NUMBER
window function to order each row grouped byDisplayName
(or better, the underlying primary key, if different andDisplayName
isn't indexed), then pick out the latest using the result:That said, there are a few oddities with your query
LIKE
for filtering onDisplayName
, yet specify a simple string rather than a patternTimeSampled
means, if the most recent entry for aDisplayName
is more than five minutes ago, it won't be returned despite being the most recent entry, which goes against how you described the requirementsNOLOCK
is a magic bullet (https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere)