Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Posts
    8

    Unanswered: Select only most recent date

    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)




    Code:
    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT CPA.ScanName
         , CPA.pspplMSSeverity
         , CPA.smachIPAddress
         , SDA.PatchMissing
         , CPA.ScanDate
      FROM qryReportsCondensedPatchesAggregate CPA
    INNER 
      JOIN qryReportsScanSummaryWithDetailsAggregate SDA
        ON SDA.smachIPAddress = CPA.smachIPAddress
     WHERE CPA.ScanName LIKE '%mgmt%'
       AND CPA.ScanDate =
           ( SELECT MAX(ScanDate)
               FROM qryReportsCondensedPatchesAggregate )
    look, ma, no GROUP BY or ORDER BY clause!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2012
    Posts
    8
    Thanks for the response, I ran this query and it return 0 records.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's likely because of your other WHERE condition

    removes it, or else also adds it to the subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •