LINQ Max

[Bill McCormick]

How would you structure a Linq statement using Max to return the record with
the greatest (newest) datetime field?

 

[Hans Kesting]

Bill McCormick wrote on 29-6-2009 :

How would you structure a Linq statement using Max to return the record with
the greatest (newest) datetime field?

Why not sort it (descending, so that the newest date is on top) and
then take the first?

Hans Kesting

 

[Gregory A. Beamer]

How would you structure a Linq statement using Max to return the
record with the greatest (newest) datetime field?

Try:
http://msdn.microsoft.com/en-us/vbasic/bb737922.aspx

They are in VB, but it is fairly straighforward to convert, as boht use
extension methods.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Bill McCormick]

Bill McCormick wrote on 29-6-2009 :

Why not sort it (descending, so that the newest date is on top) and then
take the first?

Because I like to be fancy That, and I'd like to learn how these things work.

Thanks.

 

[Bill McCormick]

Try:
http://msdn.microsoft.com/en-us/vbasic/bb737922.aspx

They are in VB, but it is fairly straighforward to convert, as boht use
extension methods.

Thanks. I just found the exact same page before I saw your post and it was
helpful. Here's what I did:

var ctare = (from t in TTares
where (t.TTareDateTime) == (from r in TTares
select r.TTareDateTime).Max()
select t.TTareWeight).Single<double>();

I don't like that it's making the comparison though. I think there must be a
more succinct method.

Thanks.

 

[Martin Honnen]

Thanks. I just found the exact same page before I saw your post and it
was helpful. Here's what I did:

var ctare = (from t in TTares
where (t.TTareDateTime) == (from r in TTares
select r.TTareDateTime).Max()
select t.TTareWeight).Single<double>();

It might be more efficient to compute the maximum only once outside of
the where predicate e.g.
DateTime latest = (from r in TTares select r.TTareDateTime).Max();

var ctare = (from t in TTares
where t.TTareDateTime == latest
select t.TTareWeight).Single<double>();

 

[Bill McCormick]

It might be more efficient to compute the maximum only once outside of
the where predicate e.g.
DateTime latest = (from r in TTares select r.TTareDateTime).Max();

var ctare = (from t in TTares
where t.TTareDateTime == latest
select t.TTareWeight).Single<double>();

OK. Thanks, I like that. But still, there must be some other way to do this
with having to compare the DateTime field. If not not, maybe I use the
previously suggested Top 1 method.

Bill

 

[Bill McCormick]

Because I like to be fancy That, and I'd like to learn how these
things work.

Have learned that the other alteratives aren't as good, I've come around to
your suggestion:

var ctare = (from t in TTares
orderby t.TTareDateTime descending
select t.TTareWeight).First();

Thanks,

Bill

 

[vanderghast]

Strange, if I compare the two syntaxes, the TOP 1 is about twice slower than
the subquery. With the batch (show execution plan):

SELECT TOP 1 * FROM ParamsProjetDefauts ORDER BY TimeStampRef DESC;
SELECT * FROM ParamsProjetDefauts WHERE TimeStampRef = (SELECT
MAX(TimeStampRef) FROM ParamsProjetDefauts);


the first query, accordingly to the query execution plan, take 68% of the
time to run the whole batch (while the subquery takes only 32 % of the batch
time). The solution you adopted is (probably) translated to the TOP 1, case.



Vanderghast, Access MVP

 

[Bill McCormick]

Strange, if I compare the two syntaxes, the TOP 1 is about twice slower
than the subquery. With the batch (show execution plan):

SELECT TOP 1 * FROM ParamsProjetDefauts ORDER BY TimeStampRef DESC;
SELECT * FROM ParamsProjetDefauts WHERE TimeStampRef = (SELECT
MAX(TimeStampRef) FROM ParamsProjetDefauts);


the first query, accordingly to the query execution plan, take 68% of
the time to run the whole batch (while the subquery takes only 32 % of
the batch time). The solution you adopted is (probably) translated to
the TOP 1, case.

Agreed, the TOP 1 translates. And the tining results are surprising. I'll do
some testing here to try to duplicate your results.

Thanks.