如何从具有最新日期戳的JOIN中获取记录集？

德鲁怀亚特

我正在写一个从3个表中获取一些数据的存储过程。现在，我的输出如下所示：

sql输出

钻机20被列出两次。我只想获取具有最新日期戳的记录。所以我的查询现在看起来像这样：

SELECT
      robinson_Rigs.rigId
    , robinson_Rigs.rigName
    , robinson_Clients.companyName
    , robinson_Wells.wellName
    , robinson_Wells.county
    , max(robinson_Wells.startDate)
    , robinson_Wells.directions
FROM robinson_Wells
    JOIN robinson_Rigs ON robinson_wells.rigId = robinson_Rigs.rigId
    JOIN robinson_Clients on robinson_Wells.clientId = robinson_Clients.clientId
group by robinson_Rigs.rigId
ORDER BY robinson_Rigs.rigId

但我收到此错误：

Msg 8120, Level 16, State 1, Procedure robinson_GetAllDrivingDirections, Line 14
Column 'robinson_Rigs.rigName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

我怎样才能做到这一点？

安德鲁·凯勒

如果任何其他字段包含该装备的其他值，则仅对除StartDate以外的所有内容进行简单分组仍然会返回一个装备的多行。

而是尝试这样的事情：

SELECT
      robinson_Rigs.rigId
    , robinson_Rigs.rigName
    , robinson_Clients.companyName
    , robinson_Wells.wellName
    , robinson_Wells.county
    , robinson_Wells.startDate
    , robinson_Wells.directions
FROM robinson_Wells
    JOIN robinson_Rigs ON robinson_wells.rigId = robinson_Rigs.rigId
    JOIN
    (
        SELECT rigId
            , MAX(startDate) AS MostRecentDate
        FROM robinson_rigs
        GROUP BY rigId
    ) latestRigDate ON robinson_Rigs.RigId = latestRigDate.RigId
        AND robinson_rigs.StartDate = latestRigDate.MostRecentDate
    JOIN robinson_Clients on robinson_Wells.clientId = robinson_Clients.clientId
ORDER BY robinson_Rigs.rigId