我有一个表,它有以下列
> Id
> ForeignKeyId
> AttributeName
> AttributeValue
>创建
一些数据可能如下所示：
1,1,'EmailPreference','Text',1/1/2010 2,'Html',1/3/2010 3,1/10/2010 4,2,1/2/2010 5,1/8/2010
我想运行一个查询,为每个不同的ForeignKeyId和AttributeName提取AttributeValue列的最新值,使用Created列来确定最近的值.输出示例为：
ForeignKeyId AttributeName AttributeValue Created ------------------------------------------------------- 1 'EmailPreference' 'Text' 1/10/2010 2 'EmailPreference' 'Html' 1/8/2010
如何使用sql Server 2005？
解决方法
单程
select t1.* from (select ForeignKeyId,AttributeName,max(Created) AS MaxCreated from YourTable group by ForeignKeyId,AttributeName) t2 join YourTable t1 on t2.ForeignKeyId = t1.ForeignKeyId and t2.AttributeName = t1.AttributeName and t2.MaxCreated = t1.Created
另请参阅Including an Aggregated Column’s Related Values 5种不同的方式来进行这种查询
