CODEWITHSUNDEEP
c#linq
sooprise
sooprise

Reputation: 23187

LINQ Query Grabs Earliest Entry Per Other Value

Let's say I have a database that has a bunch of stock quotes

TableQuotes
2010-07-22 09:45:00.000, "ABC", 102.23
2010-07-22 09:45:00.000, "EFG", 24.65
2010-07-22 09:45:00.000, "HIJ", 14.20
2010-07-22 10:45:00.000, "ABC", 104.25
2010-07-22 10:45:00.000, "EFG", 26.09
2010-07-22 10:45:00.000, "HIJ", 12.43
2010-07-23 09:45:00.000, "ABC", 101.23
2010-07-23 09:45:00.000, "EFG", 23.65
2010-07-23 09:45:00.000, "HIJ", 16.20
2010-07-23 10:45:00.000, "ABC", 99.26
2010-07-23 10:45:00.000, "EFG", 22.09
2010-07-23 10:45:00.000, "HIJ", 11.43
...

I want to know how to write a query that:
1. Grabs only one stock quote per symbol per day
2. Grabs the earliest stock quote of each day for each symbol

so for instance, the desired result from my example table would be:

Result
2010-07-22 09:45:00.000, "ABC", 102.23
2010-07-22 09:45:00.000, "EFG", 24.65
2010-07-22 09:45:00.000, "HIJ", 14.20
2010-07-23 09:45:00.000, "ABC", 101.23
2010-07-23 09:45:00.000, "EFG", 23.65
2010-07-23 09:45:00.000, "HIJ", 16.20

Upvotes: 4

Views: 103

Answers (3)

sgriffinusa
sgriffinusa

Reputation: 4221

The following will return the requested solution, the earliest quote for each company on a given day.

var results = from q in quotes
              group q by new { q.Symbol, q.TimeStamp.Date } into c
              select new TableQuote()
              {
                  Symbol = c.Key.Symbol,
                  TimeStamp = c.Min(ct => ct.TimeStamp),
                  Quote = c.OrderBy(ct => ct.TimeStamp).First().Quote
              };

Upvotes: 0

JupiterP5
JupiterP5

Reputation: 328

This is a great resource for figuring out how to write the linq query you desire http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

Here's a straight copy of the example they give for your situation

var categories =
    from p in products
    group p by p.Category into g
    select new { Category = g.Key, TotalUnitsInStock = g.Sum(p => p.UnitsInStock) };

Upvotes: 0

recursive
recursive

Reputation: 86134

var result = from tq in TableQuotes
group tq by new {tq.TimeStamp.Date, tq.Symbol} into g
select g.OrderBy(tq => tq.TimeStamp).First();

Upvotes: 8

Related Questions