How to get last element by date of each type in LINQ or TSQL
Imagine the table is 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 that has a certain price. This price can be updated if necessary by adding a new line with the same ID, different price and later date.
So with a dataset like
ID Starting price
1 01/01/2009 10
1 01/01/2010 20
2 01/01/2009 10
2 01/01/2010 20
How do I get a set like the following?
1 01/01/2010 20
2 01/01/2010 20
a source to share
There are several ways to say this in SQL. Here's the one using the subquery:
SELECT *
FROM Price p
WHERE NOT EXISTS (
SELECT *
FROM Price
WHERE ID = p.ID
AND StartDate > p.StartDate
)
This is almost trivial with 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 is. I'm not in front of VS right now, so I can't verify that this is correct, or that LINQ will be able to convert it to SQL.
Minor sentence: don't use a name ID
to store a non-unique value (in this case it's a type). This is confusing.
a source to share
Since you tagged your question with LINQ to SQL
, here is a 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
};
a source to share