LINQ Query Grabs Earliest Entry Per Other Value

Question

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

score 8 · Accepted Answer · answered Jul 23 '10 at 21:43

8

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

answered Jul 23 '10 at 21:43

recursive

83,943
34
151
241

Huh? This doesn't look like it will pull just one symbol per date. – PatrickSteele Jul 23 '10 at 22:01
Won't this grab only the earliest quote for any company, not every company? – sgriffinusa Jul 23 '10 at 22:02
Ahhh yes... It will work. I see how you're grouping/selecting now. Very neat and tidy. Upvoted! – PatrickSteele Jul 23 '10 at 22:27
@sgriffinusa: no. it grabs the earliest record from each group, where the grouping criteria is date and symbol, so different symbol means different group. – recursive Jul 23 '10 at 22:36
Thanks for explaining further. I see how it works now, pretty slick. – sgriffinusa Jul 23 '10 at 22:50
The grouping takes an enormous amount of time. I appreciate the elegance of the query, but is there a faster way to do this? – sooprise Jul 26 '10 at 17:58
If performance is a concern, I'd make this query a view in the database, so you can get better control over the SQL. – recursive Jul 26 '10 at 19:43

score 0 · Answer 2 · answered Jul 23 '10 at 21:47

0

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) };

answered Jul 23 '10 at 21:47

JupiterP5

318
1
10

That's called a lambda expression. – recursive Jul 23 '10 at 21:50
anonymous delegate, using lambda syntax in this case. For a more complete answer go here -> http://stackoverflow.com/questions/978063/anonymous-delegates-in-c – JupiterP5 Jul 23 '10 at 21:52

sgriffinusa · Answer 3 · 2010-07-23T22:47:57.547

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
              };

LINQ Query Grabs Earliest Entry Per Other Value

3 Answers3