无法通过最大日期获取最新记录，寻求SQL查询解决方案

Hey there! No worries at all—first questions can always feel a bit tricky. Let's fix that query so you only get the latest inspection record for Address_Code 'GEN0021'.

The Problem With Your Current Query #

The issue here is that you're grouping by every single column you're selecting. That means SQL will create a separate group for every unique combination of those columns, and the MAX(Insp_Date) will only be the maximum within each group, not the absolute latest date across all matching records. That's why you're still getting multiple rows instead of just the one you want.

Solution 1: Filter by the Latest Date First #

First, we'll find the absolute latest inspection date for your target address, then fetch the full record(s) that match that date:

SELECT 
    A.Insp_Date AS Last_Insp_Date,
    A.Doc_ID,
    A.Service_Call_ID,
    A.Customer_ID,
    A.Address_Code,
    A.State,
    A.Branch,
    B.HydLoc,
    B.FlwOutSz,
    B.StaticPSI,
    B.ResidualPSI,
    B.PititPSI,
    B.FlwGPM
FROM [dbo].[fofHydrntInspHdr] AS A
LEFT OUTER JOIN [dbo].[fofHYD2800FlwTstRT] AS B ON A.Doc_ID = B.Doc_ID
WHERE A.Address_Code = 'GEN0021' 
  AND A.Doc_ID > 0
  AND A.Insp_Date = (
      -- Subquery to get the absolute latest inspection date for the address
      SELECT MAX(Insp_Date) 
      FROM [dbo].[fofHydrntInspHdr]
      WHERE Address_Code = 'GEN0021' AND Doc_ID > 0
  )

Solution 2: Handle Multiple Records on the Same Latest Date #

If there might be multiple inspections on the same latest date and you want only the most recent one (using Doc_ID as a tiebreaker, since you mentioned trying MAX(Doc_ID)), use a window function like ROW_NUMBER():

WITH RankedInspections AS (
    SELECT 
        A.Insp_Date,
        A.Doc_ID,
        A.Service_Call_ID,
        A.Customer_ID,
        A.Address_Code,
        A.State,
        A.Branch,
        B.HydLoc,
        B.FlwOutSz,
        B.StaticPSI,
        B.ResidualPSI,
        B.PititPSI,
        B.FlwGPM,
        -- Rank records by latest date first, then latest Doc_ID
        ROW_NUMBER() OVER (ORDER BY A.Insp_Date DESC, A.Doc_ID DESC) AS rn
    FROM [dbo].[fofHydrntInspHdr] AS A
    LEFT OUTER JOIN [dbo].[fofHYD2800FlwTstRT] AS B ON A.Doc_ID = B.Doc_ID
    WHERE A.Address_Code = 'GEN0021' 
      AND A.Doc_ID > 0
)
SELECT 
    Insp_Date AS Last_Insp_Date,
    Doc_ID,
    Service_Call_ID,
    Customer_ID,
    Address_Code,
    State,
    Branch,
    HydLoc,
    FlwOutSz,
    StaticPSI,
    ResidualPSI,
    PititPSI,
    FlwGPM
FROM RankedInspections
WHERE rn = 1 -- Only keep the top-ranked (latest) record

Both approaches will give you just the single latest record you're looking for, depending on whether you need to handle ties on the latest date.

内容的提问来源于stack exchange，提问作者SQLNoob