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