StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL/LINQ Select all with group by 2 columns
text
Body
copied!<p>I tried to find answer for my problem, but I couldn't. I have table with ID, WorkerID, Function(int), and DateTime. Each worker has multiple records for each day. I need to get only first record for each day, for all workers. This query gives the results I wanted:</p> <pre><code>SELECT WorkerID, MIN(DateTime) AS DateTime FROM Records GROUP BY WorkerID, LEFT(DateTime, 10) ORDER BY WorkerID </code></pre> <p>But I want to select all columns (ID, WorkerID, Function, and DateTime). How can I do that? Also, I need this query for ASP.NET MVC3 application, so if anyone could write this for me in LINQ, and explain how to transform that query into IEnumerable(Record) or IQuerible(Record), I would be very thankful. Thanks in advance guys, hope you can save me.</p> <p>Edit: Your LINQ query worked, but I had a lot of issues regarding perfomance, because of using a lot of Views, not Tables. Now I switched to Table, and I want do query directly on Table. Structure is the same, just instead of WorkerID, I have ETAG, which is the same. But I have problem now. SQL query mentioned above, gives me about 50k results on table, which is correct, but now your LINQ query gives me only 9k, so something is wrong. Here is query:</p> <pre><code>from d in Records group d by new { d.ETAG, Date = d.DateTime.Value.Year + d.DateTime.Value.Month + d.DateTime.Value.Day } into g orderby g.Key.ETAG select g.OrderBy(e => e.DateTime).FirstOrDefault() </code></pre> <p>Please, can you help me with this one...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload