CODEWITHSUNDEEP
sqlsql-server
user3711442
user3711442

Reputation: 233

Getting latest date for multiple records

I am having a hard time getting the latest results for various records in SQL Server 2008. I'm trying to use MAX(), but it is only returning a few results when there should be over 1,000.

EDIT: The results include computer names and the last usage information. It is for software metering results for SCCM. The line "mf.SecurityKey = 'CSA00107'" is basically just looking for software metering results for Microsoft Visio. However, there are various instances of "Last Used" dates for each computer. I need the LAST "Last Used" date so there is one last usage record for each computer name.

Query:

select distinct vrs.Name0,mus.LastUsage
from v_MeteredFiles mf
     inner join v_MonthlyUsageSummary mus on mus.FileID = mf.MeteredFileID
     left outer join v_R_System vrs on vrs.ResourceID = mus.ResourceID
where mf.SecurityKey = 'CSA00107' and
      DateDiff(day, mus.LastUsage, GetDate()) > 60 and
      mus.LastUsage in (select max(LastUsage) from v_MonthlyUsageSummary group by ResourceID)

When I remove the last line, I receive all of the results. However, since there are various "LastUsage" records for a single "Name0", I'm getting too many results. I need the LATEST LastUsage for each machine. Any help would be greatly appreciated.

Upvotes: 0

Views: 278

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use the row_number function instead of a sub-query.

select Name0, LastUsage 
from (
select vrs.Name0, mus.LastUsage,
row_number() over(partition by vrs.Name0 order by mus.lastusage desc) as rn
from v_MeteredFiles mf
inner join v_MonthlyUsageSummary mus on mus.FileID = mf.MeteredFileID
left outer join v_R_System vrs on vrs.ResourceID = mus.ResourceID
where mf.SecurityKey = 'CSA00107' and
DateDiff(day, mus.LastUsage, GetDate()) > 60 ) t
where rn = 1;

Edit : As per OP's request in the comments

select Name0, LastUsage 
from (
select vrs.Name0, mus.LastUsage,
row_number() over(partition by vrs.Name0 order by mus.lastusage desc) as rn
from v_MeteredFiles mf
inner join v_MonthlyUsageSummary mus on mus.FileID = mf.MeteredFileID
left outer join v_R_System vrs on vrs.ResourceID = mus.ResourceID
where mf.SecurityKey = 'CSA00107' ) t
where rn = 1 
and DateDiff(day, LastUsage, GetDate()) > 60

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

I think you should replace in with =

And internal Select need reference to outside value.

select distinct vrs.Name0,mus.LastUsage
from v_MeteredFiles mf
inner join v_MonthlyUsageSummary mus 
     on mus.FileID = mf.MeteredFileID
left outer join v_R_System vrs 
     on vrs.ResourceID = mus.ResourceID
where mf.SecurityKey = 'CSA00107' 
  and DateDiff(day, mus.LastUsage, GetDate()) > 60 
  and mus.LastUsage **=** (select max(LastUsage) 
                           from v_MonthlyUsageSummary IMUS
                         **where IMUS.ResourceID = mus.ResourceID**)

Upvotes: 0

Related Questions