May 31, 2011 at 3:19 am
I've got table with thousands of records, and like to have for each asset 1 record returned (Most recent record)
Every asset is sending data every 3 hours to this table and I would like to get the most recent record returned.
I've got logdate time column however if I use Max (logdatetime) it returns every record for that machine.
I've used sub query where I first find the most recent record for particular asset, and then use WHERE clausule to return
the other columns from that table, that does not work.
Sample query I used
select CompName, JobName, LoggedOnUser, Status, IPSubnetSiteDesc, logDateTime from tblSample (NOLOCK)
where logDateTime IN (SELECT MAX(logdatetime) from tblSample (NOLOCK)
where CompName = 'DIMP126'
group by compname)
this returns 2 records, it makes sense if another record has same logdate time.
this table contains info about 100,000 assets and I like to have the most recent info for each asset.
any idea or suggestion are welcome.
May 31, 2011 at 3:44 am
I take it that the CompName column contains the name of the asset? If so, just remove the WHERE clause - you want to return data for all assets, not just the one specified in your query.
John
Edit: Actually, you want something like this:
select CompName, JobName, LoggedOnUser, Status, IPSubnetSiteDesc, logDateTime from tblSample s
JOIN (SELECT CompName, MAX(logdatetime) from tblSample
group by compname) m
ON m.logdatetime = s.logdatetime
AND m.CompName = s.CompName
Oh, and don't use NOLOCK in production code unless you don't mind getting inaccurate results.
May 31, 2011 at 4:17 am
Hi John,
thx for the quick response, that did the trick.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply