Grouping with SubSonic 3

Question

I have the following table "GroupPriority":

Id   Group   Priority
 1       1         0
 2       2         0
 3       3         0
 4       2         1
 5       1         1
 6       2         2
 7       3         1

I would like to group these on "Group", order them by "Priority" and then get the one in each "Group" with the highest Priority with the use of linq and subsonic 3.

In this case the result would be:

Id   Group   Priority
 5       1          1
 6       2          2
 7       3          1

The sql would look like this:

SELECT *
FROM   GroupPriority
WHERE  (Priority =
                  (SELECT  MAX(Priority)
                   FROM    GroupPriority
                   WHERE   (Group = GroupPriority.Group)))

Thanks

Answer 1

Got the solution:

    var group_query = new Query<GroupPriority>(provider);
    var items = from gp in group_query
                where gp.Priority == 
                    (from gp_sub in group_query
                     where gp_sub.Group == gp.Group
                     select gp_sub.Priority).Max()
                select gp;