Link to home
Start Free TrialLog in
Avatar of hijiki7777
hijiki7777

asked on

LINQ database query using max

In the code I find the maximum date from a table by using
DateTime maxDate = Result.Select(md => md.Date).Max();

However I want to find the next largest date.
In SQL it will be
SELECT MAX([Date]) FROM dbo.FundPrice WHERE [Date] < '2008/02/04'

I am unfamilier with the Linq syntax. I cannot seem to put in a WHERE clause, so how do I do it?
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Avatar of hijiki7777
hijiki7777

ASKER

I would say that these are excellent links that I missed during my own research, so thank you for that. But none seem to match the situation I need resolving.
If I had a couple of hours spare I may be able to work it out, but for now I do not.
What object are you working with LINQ on?

Bob
SQL
LINQ is a syntax that generally works with business objects, so are you getting the data into a business objects, that you can work with (i.e. Employee), so that you can use the language-specific syntax for calculating Max, applying restriction operators, etc.

Bob
DateTime maxDate = Result.Select(md => md.Date).Max();

I want to convert the above to do this SQL below, where the date is the maximum date. I have that date as a DateTime field in C# (ie maxDate as above), and I expect to use that field to obtain the next highest date.
SELECT MAX([Date]) FROM dbo.FundPrice WHERE [Date] < '2008/02/04'
To be clear, imagine the maximim date is '2008/02/04' and is in the maxDate field.
I want to populate a previousMaxDate field in a similar way as in the first line.
So although this works;
DateTime maxDate = Result.Select(md => md.Date).Max();

And gets me the largest date, I cannot find the second largest date unless I put the SQL data into a business object?
What is 'Result' defined as?

Bob
A query as follows

            // DB calls for FundPrice
            LipperPerformanceDataContext context = new LipperPerformanceDataContext();
            var Result = from c in context.FundPrices
                         where c.LipperID == investmentTrustLipperID
                         select c;

I suppose I could do a similar query, filtering out the rows with the maximum dates.
That would be one way of doing it, but it means another query.
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Have you tried the following :

dim qry = from item in table _
               select distinct date_field take 2 _
               order by date_field desc

the first record retreived would be the max date, the second one would be the next "max" date.

Carl
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial