Better way of grouping rows by the latest entry within a group using LINQ

Say I have a table with 3 cols: ActionId, uid & created.

I want to group actions by the uid, but every time a new action is inserted into a group (by uid), it will push the group upto the top, and individual rows within that group ordered.

This is what I came up with in SQL:

select * from actions as a
inner join 
(   
    select aa.[uid], MAX(aa.[created]) as maxcr开发者_JAVA技巧eated 
    from actions as aa
    group by aa.[uid]
) as a2 on a2.uid = a.uid
order by a2.maxcreated desc, a.created desc

Is there a better way to achieve this in SQL, and also then how to do this in LINQ?

---

So you want each group ordered internally, and the groups order by the latest value, right? Okay, I think we can do that...

var query = from action in actions
            group action by action.Uid into g
            orderby g.Max(action => action.Created) descending
            select new { Uid = g.Key,
                         Actions = g.OrderByDescending(action => action.Created) };

foreach (var group in query)
{
    Console.WriteLine("Uid: {0}", group.Uid);
    foreach (var action in group.Actions)
    {
        Console.WriteLine("  {0}: {1}", action.Created, action.ActionId);
    }
}

---

For the SQL, get the sort column in the SELECT statement

SELECT *, (SELECT MAX(created) FROM actions a2 where a.uid = a2.uid) AS MaxCreated
FROM actions a
ORDER BY MaxCreated desc, a.created desc

or

SELECT *
FROM actions a
ORDER BY (SELECT MAX(created) FROM actions a2 where a.uid = a2.uid) desc, a.created desc

(just fixed an error in the first query)

Here's my linq:

var actions = (from a in actions                
               orderby ((from a2 in actions
                         where a2.UserID == a.UserID
                         select a2.created).Max ()) descending, a.created descending
               select a);