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.
| Author |
Topic |
|
EDB
Starting Member
1 Post |
 Posted - 2011-10-28 : 10:08:59
|
| Hi Everyone I am new here. The database I am working with is used for vulnerability management. Our vulnerability scanner runs nightly and logs it's findings to the database. I need to design a query that will select the most recent finding based on the IP address. I do not need help with any of the joining or other aspects.Some of the columns I can use for this are IP address, Job start Time, vulnerability Name, JobID. So I want to show all IP's with there outstanding vulnerabilities. The most recent Job Start Time is what I'm thinking. Job start time corresponds to when the scan began. If you can help it would be much appreciated. If more details are needed please let me know. Thanks! |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-28 : 11:02:00
|
| [code]select [IP address], [Job start Time], [vulnerability Name], JobIDfrom(select row_number() over (partition by [IP address] order by [Job start Time] desc) as Rn,[IP address], [Job start Time], [vulnerability Name], JobIDfrom yourtable)twhere rn=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Selecting Most Recent Record Based on a Value