How to use LINQ (or TSQL) to group dataset but include top item and counts

I'm struggling with how to approach this. I have your basic dataset returned by a TSQL (SQL2005) query where the data conta开发者_开发技巧ins a list of master and detail items. Pretty vanilla, you have your master table joined to your detail table so you might get mulitple rows back per master record.

Something like:

ID / Item Descr / Subitem Descr
1 /  Jane Doe / shoes
1 /  Jane Doe / hats
2 /  John Smith / hats

What I'd like to get to do is "flatten" that out a bit. So something like:

ID / Item Descr / Count / Most Recent Subitem
1 / Jane Doe / 2 / shoes
2 / John Smith / 1 / hats

Any suggestions on the sql query, or perhaps a LINQ query that I could run on the dataset I get back from the initial sql query...?

---

var q = from i in Context.Items
        group i by i.ItemDescr into g
        select new 
        {
            ID = g.FirstOrDefault().ID,
            ItemDescr = g.Key,
            Count = g.Count(),
            MostRecentSubItem = g.FirstOrDefault().SubitemDescr // you don't show how to pick the "most recent"
        };

---

Not quite sure what you mean by "top" but this is a start if you want to do it in SQL:

SELECT id, ItemDesc, COUNT(*), MAX(SubItemDesc)
FROM MyTable
GROUP BY id, ItemDesc

---

I ended up using a correlated sub-query, which always make me nervous from a performance perspective. I don't have numbers to back that up and am not great at reading Execution Plans, it's just a gut feeling.

Lot's of good stackoverflow examples on it that got me down that path(like: Advanced SQL query with sub queries, group by, count and sum functions in to SQLalchemy and Variant use of the GROUP BY clause in TSQL)

I was wondering if using any of the new windowing functions (row_number() or rank()) would have helped, but I didn't get very far down that line.

I still wonder if it would be better to just pull back all the master-detail rows, then "post process" them using LINQ would be better. Our SQL Server would throw back that dataset to the web server pretty darn fast, then "distribute" the work load by doing LINQ on the web server, finally binding it all up to my ASP.NET gridview and sending it the client. A lot of moving parts, but could be interesting...