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

问题描述

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

I have a table that has the following columns

• 身份证
• 外键 ID
• 属性名称
• 属性值
• 创建

某些数据可能如下所示:

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

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

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屋！