SQL Server仅使用最新值选择不同的行 [英] SQL server select distinct rows using most recent value only

问题描述

我有一个包含以下各列的表

I have a table that has the following columns

• 编号
• ForeignKeyId
• AttributeName
• AttributeValue
• 已创建

一些数据可能看起来像这样:

Some of the data may look like this:

1, 1, 'EmailPreference', 'Text', 1/1/2010
2, 1, 'EmailPreference', 'Html', 1/3/2010
3, 1, 'EmailPreference', 'Text', 1/10/2010
4, 2, 'EmailPreference', 'Text', 1/2/2010
5, 2, 'EmailPreference', 'Html', 1/8/2010

我想运行一个查询，该查询使用Created列来确定每个不同的ForeignKeyId和AttributeName的AttributeValue列的最新值.输出示例为:

I'd like to run a query that pulls the most recent value of the AttributeValue column for each distinct ForeignKeyId andAttributeName, using the Created column to determine the most recent value. Example output would be:

ForeignKeyId AttributeName    AttributeValue Created
-------------------------------------------------------
1           'EmailPreference' 'Text'         1/10/2010
2           'EmailPreference' 'Html'         1/8/2010

如何使用SQL Server 2005做到这一点?

How can I do this using 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

另请参见包括汇总列的相关值，提供了5种不同的查询方式

See also Including an Aggregated Column's Related Values for 5 different ways to do this kind of query

这篇关于SQL Server仅使用最新值选择不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！