I have to query this table:
symbol time
------ ----------
aaa 2013-04-18 09:10:28.000
bbb 2013-04-18 09:10:27.000
aaa 2013-04-18 09:10:27.000
bbb 2013-04-18 09:10:26.000
I need one row for all distinct symbols having the biggest time value. How do I have to write my linq query?
Thanks in advance,
C# Linq Distinct() method removes the duplicate elements from a sequence (list) and returns the distinct elements from a single data source. It comes under the Set operators' category in LINQ query operators, and the method works the same way as the DISTINCT directive in Structured Query Language (SQL).
LINQ Distinct is not that smart when it comes to custom objects. All it does is look at your list and see that it has two different objects (it doesn't care that they have the same values for the member fields). One workaround is to implement the IEquatable interface as shown here.
The Distinct() method works the same way as the DISTINCT directive in SQL. It returns a new sequence containing all the elements from the target sequence that are distinct from each other, as determined by the default equality comparer for the data type of the sequence.
The Distinct operator returns the set which does not contain duplicate values. Or in other words, we can say that this operator removes all the duplicate values from the set or collection and return a set or collection which contain unique or dissimilar values.
Group rows by symbol and then select from each group item with max time (Table is your database table name from context):
from r in Table
group r by r.symbol into g
select g.OrderByDescending(x => x.time).First()
Same with method syntax:
Table.GroupBy(r => r.symbol)
.Select(g => g.OrderByDescending(x => x.time).First());
try out this
var q = MyTable.GroupBy(x => x.symbol )
.Select(g => g.OrderByDescending(i => i.time).First());
or use max like this
var data = from r in MyTable
group r by r.symbol into g
select new { name= g.Key, data= g.Max(a=>a.time) };
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With