Select Statement Issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select Statement Issues

Author

Topic

danSed
Starting Member

2 Posts

Posted - 2010-08-03 : 12:55:29

I am trying to select the newest date modified for each Netbios_Name0

US00032BOS51 3.2.0.1 2008-11-08 13:48:14.000
US00032BOS51 4.1.0.2 2010-01-07 12:23:20.000
US00037BOS51 4.1.0.2 2010-01-07 12:23:20.000
US00037BOS51 3.2.0.1 2008-11-08 12:48:14.000

Here is my current query which does not remove the older records:

SELECT distinct SYS.Netbios_Name0 , SF.FileVersion, SF.FileModifiedDate
FROM v_GS_SoftwareFile as SF
inner join v_R_System as SYS on SYS.ResourceID = SF.ResourceID
WHERE FileName = 'rdmclient.exe'
and SF.FileModifiedDate in
(SELECT max(FileModifiedDate) from v_GS_SoftwareFile
where FileName = 'rdmclient.exe'
group by ResourceID)
order by SYS.Netbios_Name0

Any suggestions would be much appreciated.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-03 : 13:30:02

Maybe this?

select t.Netbios_Name0,t.FileVersion,t.FileModifiedDate
from
(
SELECT ROW_NUMBER() OVER(PARTITION BY SYS.Netbios_Name0 ORDER BY SF.FileModifiedDate DESC) AS seq
,SYS.Netbios_Name0 
,SF.FileVersion
,SF.FileModifiedDate
FROM v_GS_SoftwareFile as SF
inner join v_R_System as SYS on SYS.ResourceID = SF.ResourceID
WHERE FileName = 'rdmclient.exe' 
) t
where t.seq = 1

danSed
Starting Member

2 Posts

Posted - 2010-08-03 : 14:21:44

That is exactly what I was looking for.

Thanks a bunch!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-03 : 14:22:42

Np. You are welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources