SQL server select distinct rows using most recent value only

Question

I have a table that has the following columns

• Id
• ForeignKeyId
• AttributeName
• AttributeValue
• Created

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

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

How can I do this using SQL Server 2005?

Answer 1

One way

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

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