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?
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?
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.
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
Bob
ASKER
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
Bob
ASKER
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.
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.
ASKER
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?
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
Bob
ASKER
A query as follows
// DB calls for FundPrice
LipperPerformanceDataConte xt context = new LipperPerformanceDataConte xt();
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.
// DB calls for FundPrice
LipperPerformanceDataConte
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
101 LINQ Samples
http://msdn2.microsoft.com/en-us/vcsharp/aa336746.aspx
Restriction Operators
http://msdn2.microsoft.com/en-us/vcsharp/aa336760.aspx
Bob