这必须是一个无脑的，但我被难住了。我习惯在msaccess中使用聚合'first'，但mysql没有这样的功能。这是一张简单的table。我想根据日期返回每个唯一“组id”的最新记录。我需要黄色的三张唱片。
我被要求添加我的完整查询。我尝试了其中一个建议，使用联接功能将“t”替换为临时表名，但失败。“无法重新打开表“t”，代码如下。我知道这很难看，但它确实返回了正确的数据集。
我清理了代码并添加了连接代码。错误：“无法重新打开表't'”

enter code here
    DROP TABLE IF EXISTS `tmpMaxLookupResults`;

创建临时表tmpmaxlookupults为

SELECT 
REPORTS.dtmReportCompleted,
RESULTS.lngMainReport_ID, RESULTS.lngLocationGroupSub_ID
FROM
(tbl_010_040_ProcedureVsTest_Sub as ProcVsSub
INNER JOIN tbl_010_050_CheckLog_RESULTS as RESULTS 
ON (ProcVsSub.lngLocationGroupSub_ID = RESULTS.lngLocationGroupSub_ID)
AND (ProcVsSub.lngProcedure_ID = RESULTS.lngProcedure_ID)
AND (ProcVsSub.lngItemizedTestList_ID = RESULTS.lngItemizedTestList_ID)
AND (ProcVsSub.strPasscodeAdmin = RESULTS.strPasscodeAdmin)
AND (ProcVsSub.strCFICode = RESULTS.strCFICode))
INNER JOIN
tbl_000_010_MAIN_REPORT_INFO as REPORTS ON (RESULTS.lngPCC_ID = 
REPORTS.lngPCC_ID)
AND (RESULTS.lngProcedure_ID = REPORTS.lngProcedure_ID)
AND (RESULTS.lngMainReport_ID = REPORTS.idMainReport_ID)
AND (RESULTS.strPasscodeAdmin = REPORTS.strPasscodeAdmin)
AND (RESULTS.strCFICode = REPORTS.strCFICode)
WHERE
(((RESULTS.lngProcedure_ID) = 143)
AND ((RESULTS.dtmExpireDate) IS NOT NULL)
AND ((RESULTS.strCFICode) = 'ems'))
GROUP BY RESULTS.lngMainReport_ID, RESULTS.lngLocationGroupSub_ID
ORDER BY (REPORTS.dtmReportCompleted) DESC;

SELECT t.* 
FROM tmpMaxLookupResults AS t 
JOIN (
SELECT lngLocationGroupSub_ID, 
MAX(dtmReportCompleted) AS max_date_completed 
FROM tmpMaxLookupResults 
GROUP BY lngLocationGroupSub_ID ) AS dt 
ON dt.lngLocationGroupSub_ID = t.lngLocationGroupSub_ID AND 
dt.max_date_completed = t.dtmReportCompleted
enter code here