How to get the last element by date of each "type" in LINQ or TSQL

Question

Imagine to have a table defined as

CREATE TABLE [dbo].[Price](
    [ID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [Price] [int] NOT NULL
)

where ID is the identifier of an action having a certain Price. This price can be updated if necessary by adding a new line with the same ID, different Price, and a more recent date.

So with a set of a data like

ID StartDate Price

1  01/01/2009 10
1  01/01/2010 20
2  01/01/2009 10
2  01/01/2010 20

How to obtain a set like the following?

1  01/01/2010 20
2  01/01/2010 20

Answer 1

In SQL, there are several ways to say it. Here's one that uses a subquery:

SELECT *
  FROM Price p
 WHERE NOT EXISTS (
        SELECT *
          FROM Price
         WHERE ID = p.ID
           AND StartDate > p.StartDate
       )

This translates fairly trivially to LINQ:

var q = from p in ctx.Price
        where !(from pp in ctx.Price
                where pp.ID == p.ID
                   && pp.StartDate > p.StartDate
                select pp
               ).Any()
        select p;

Or should I say, I think it does. I'm not in front VS right now, so I can't verify that this is correct, or that LINQ will be able to convert it to SQL.

Minor quibble: Don't use the name ID to store a non-unique value (the type, in this case). It's confusing.

Answer 2

Assuming ID & StartDate will be unique:

SELECT p.ID, p.StartDate, p.Price
FROM Price p
    JOIN 
    (
        SELECT ID, MAX(StartDate) AS LatestDate
        FROM Price
        GROUP BY ID
    ) p2 ON p.ID = p2.ID AND p.StartDate = p2.LatestDate

Answer 3

Since you tagged your question with LINQ to SQL, here is an LINQ query to express what you want:

from price in db.Prices
group price by price.Id into group
let maxDateInGroup = group.Max(g => g.StartDate)
let maxDatePrice = group.First(g => g.StartDate == maxDateInGroup)
select
{
    Id = group.Key,
    StartDate = maxDatePrice.StartDate,
    Price = maxDatePrice.Price
};