CODEWITHSUNDEEP
sqlsql-server
Boris Urban
Boris Urban

Reputation: 13

SQL select newest record in SQL Server

I am using the following MS SQL query

SELECT Top(100) 
    DateTime, DisplayName, FullName,SampleValue
FROM 
    OperationsManagerDW.dbo.vManagedEntity,
    OperationsManagerDW.dbo.vPerformanceRule,
    OperationsManagerDW.dbo.vPerformanceRuleInstance,
    OperationsManagerDW.Perf.vPerfRaw
WHERE 
    vPerfRaw.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
    AND vPerfRaw.PerformanceRuleInstanceRowId = vPerformanceRuleInstance.PerformanceRuleInstanceRowId
    AND vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
    AND vPerformanceRule.ObjectName = 'Memory'
    AND vPerformanceRule.CounterName = 'PercentMemoryUsed'
ORDER BY 
    DateTime DESC,Displayname, FullName

And I receive this

DateTime                     FullName    SampleValue
---------------------------------------------------------
01.09.2014  13:23:29.200    N17.DE1.LOC 162.007
01.09.2014  13:18:29.217    N17.DE1.LOC 160.298
01.09.2014  13:18:29.187    N17.DE1.LOC 159.816
01.09.2014  13:14:24.973    X-OM01.DE1.LOC  285.489
01.09.2014  13:09:24.930    X-OM01.DE1.LOC  304.142
01.09.2014  12:58:29.323    N17.DE1.LOC 159.469
01.09.2014  12:58:29.277    N17.DE1.LOC 159.671
01.09.2014  12:34:38.157    DC1.DE1.LOC 40.221

but I only need the newest entries of the servers (see FullName):

01.09.2014  13:23:29.200    N17.DE1.LOC         162.007
01.09.2014  13:14:24.973    X-OM01.DE1.LOC      285.489
01.09.2014  12:34:38.157    DC1.DE1.LOC        40.221

Please need help. Regards

Upvotes: 0

Views: 1704

Answers (2)

Alfons
Alfons

Reputation: 531

As an alternative to Kiran you could also do it using a subquery, also using joins make it better readable.

select [fields] from
  ( Select top(100) datetime, id 
    from table1
    order by datetime desc ) T
inner join [other tables]
  on [join condition]
order by datetime desc, displayname, fullname

Upvotes: 0

Kiran Hegde
Kiran Hegde

Reputation: 3681

You can use the row_number function for this. Try the following query. I assume that you need the latest record for each part name. 

WITH data
AS
(
SELECT Top(100) DateTime, DisplayName, FullName,SampleValue,
ROW_NUMBER() OVER(PARTITION BY FullName ORDER BY DATETIME DESC) AS rowNum
FROM OperationsManagerDW.dbo.vManagedEntity,
OperationsManagerDW.dbo.vPerformanceRule,
OperationsManagerDW.dbo.vPerformanceRuleInstance,
OperationsManagerDW.Perf.vPerfRaw
WHERE vPerfRaw.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
AND vPerfRaw.PerformanceRuleInstanceRowId = vPerformanceRuleInstance.PerformanceRuleInstanceRowId
AND vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
AND vPerformanceRule.ObjectName = 'Memory'
AND vPerformanceRule.CounterName = 'PercentMemoryUsed'

)
SELECT * FROM data
WHERE rowNum =1
ORDER BY [DateTime] DESC,Displayname, FullName

Upvotes: 4

Related Questions