Select only most recent date - 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

General SQL Server Forums

New to SQL Server Programming

Select only most recent date

Author

Topic

neb2886
Starting Member

4 Posts

Posted - 2012-03-08 : 10:23:37

Hi, first time poster here.

I need this query to pull just the last scan date, i.e. if a scan ran on 3/7/2012 I need just the records (there may be few or many) that pertain to that date and no other dates. When I run this query I am still getting all of the results (i.e. scan dates of 3/7/2012, 3/5/2012, 3/1/2012, etc)

Can you see something wrong that would be causing this? I just need the most recent date, even if there are many records for that date (i.e. 10 IP addresses that were all scanned on 3/5/2012)

SELECT CPA.ScanName,
CPA.pspplMSSeverity,
CPA.smachIPAddress,
SDA.PatchMissing,
MAX(CPA.ScanDate)

FROM qryReportsCondensedPatchesAggregate CPA

INNER JOIN qryReportsScanSummaryWithDetailsAggregate SDA
ON CPA.smachIPAddress=SDA.smachIPAddress

WHERE CPA.ScanName LIKE '%mgmt%'

GROUP BY CPA.ScanName, CPA.pspplMSSeverity, CPA.smachIPAddress, SDA.PatchMissing, CPA.ScanDate

ORDER BY CPA.ScanDate DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:28:35

can you show some sample data and then explain what you want?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neb2886
Starting Member

4 Posts

Posted - 2012-03-08 : 10:41:26

Column1 smachIPAddress ScanName pspplMSSeverity PatchMissing

3/5/2012 15:24 162.1.1.1 Mgmt Servers OU 0 1
3/5/2012 15:24 162.1.1.1 Mgmt Servers OU 0 1
3/5/2012 15:24 1162.1.1.1 Mgmt Servers OU 0 1
3/5/2012 15:24 162.1.1.1 Mgmt Servers OU 0 2
3/5/2012 15:24 162.1.1.1 Mgmt Servers OU 0 1
3/1/2012 17:29 162.1.1.1 Mgmt Servers OU 0 2
3/1/2012 17:29 162.1.1.1 Mgmt Servers OU 0 4
3/1/2012 17:29 162.1.1.1 Mgmt Servers OU 0 2
3/1/2012 17:29 162.1.1.1 Mgmt Servers OU 0 3
3/1/2012 17:29 162.1.1.1 Mgmt Servers OU 0 1
3/1/2012 17:29 162.1.1.1 Mgmt Servers OU 0 3

I would like the query to run and only show the most records for the most recent scan date (in this case 3/5/2012, 5 records). Right now the query is pulling all of the dates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:44:26

[code]
SELECT ScanName,
pspplMSSeverity,
smachIPAddress,
PatchMissing,
ScanDate
FROM
(
SELECT CPA.ScanName,
CPA.pspplMSSeverity,
CPA.smachIPAddress,
SDA.PatchMissing,
CPA.ScanDate,
DENSE_RANK() OVER (ORDER BY CPA.ScanDate DESC) AS Rnk

FROM qryReportsCondensedPatchesAggregate CPA

INNER JOIN qryReportsScanSummaryWithDetailsAggregate SDA
ON CPA.smachIPAddress=SDA.smachIPAddress

WHERE CPA.ScanName LIKE '%mgmt%'

)t
WHERE rnk=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neb2886
Starting Member

4 Posts

Posted - 2012-03-08 : 11:03:40

Thank you, this worked perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 11:37:47

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources