我有一组记录,我想显示符合特定条件的最新记录。过去我做错了,它会首先提取最新的记录,然后尝试匹配会导致某些记录消失的条件。我想让查询做的是首先找到符合条件的记录,然后让它从该数据集中提取最新的记录。我需要在 Access 中将此查询插入到表中。
我以为我已经解决了,但我收到错误消息“您的查询不包含指定的表达式‘SufGrpID’作为聚合函数的一部分
数据示例:
当查询运行时,我希望结果是:
数据示例:
SufGrpID 03 将从集合中删除,因为它不是 CaseID 123 的最新记录
SufGrpID 04 将从集合中删除,因为它不属于 SufTypeID 14 且不属于状态 F
How the data looks
+----------+---------+-------------------------+-----------+--------+
| SufGrpID | CaseID | CreateDate | SufTypeID | Status |
+----------+---------+-------------------------+-----------+--------+
| 01 | 123 | 2010-08-20 07:42:32.000 | 14 | F |
| 02 | 234 | 2010-04-28 10:33:56.000 | 14 | F |
| 03 | 123 | 2010-04-20 10:05:04.000 | 14 | F |
| 04 | 345 | 2010-08-20 11:18:42.000 | 12 | I |
| 05 | 345 | 2010-04-20 11:18:42.000 | 14 | F |
+----------+---------+-------------------------+-----------+--------+
Here's the code that did not work for me...
INSERT INTO [aStudent Base Data] ( [Self Suff ID], [Self Suff Create Date] )
SELECT dbo_sufscrgrp.SufGrpID, Max(dbo_sufscrgrp.CreateDate)
FROM dbo_sufscrgrp
WHERE (((dbo_sufscrgrp.SufTypeID)=14) AND ((dbo_sufscrgrp.Status)="F"))
GROUP BY dbo_sufscrgrp.CaseID;
What I'd like the results to be. (EDITED at 1:33 CST)
+--------------+------------------------+
| Self Suff ID | Self Suff Create Date |
+--------------+------------------------+
| 01 | 2010-08-20 07:42:32.000 |
| 02 | 2010-04-28 10:33:56.000 |
| 05 | 2010-04-20 11:18:42.000 |
+--------------+-------------------------+
谢谢你提供的所有帮助!
基于最小数据集示例,请考虑:
SELECT dbo_sufscrgrp.*
FROM dbo_sufscrgrp
WHERE SufGrpID
IN (SELECT TOP 1 SufGrpID FROM dbo_sufscrgrp As Dupe
WHERE Dupe.CaseID=dbo_sufscrgrp.CaseID AND SufTypeID=14 and Status="F"
ORDER BY Dupe.CreateDate DESC, Dupe.SufGrpID DESC);
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句