How can I return data from my table grouped but still in order of title?

Question

I've got a simple table in a SQL Server 2012 database and I'm querying the table using EntityFramework 6 in ASP.Net 4.51. The table has the following structure and data...

FileID       GroupID     Title             DateAdded
------------------------------------------------------
1            1           Charlie rev 1     21/05/2016
2            2           Beta rev 1        22/05/2016
3            1           Charlie rev 2     23/05/2016
4            2           Beta rev 2        24/05/2016
5            3           Alpha rev 1       25/05/2016

Basically the table represents files and revisions of files uploaded by the user, so when they view the data I'm wanting to show the first file of a group, then all older revisions in descending date order below. Ordering by GroupID and DateAdded Descending, I can get the following...

FileID       GroupID     Title             DateAdded
--------------------------------------------------------
3            1           Charlie rev 2     23/05/2016
1            1           Charlie rev 1     21/05/2016
4            2           Beta rev 2        24/06/2016
2            2           Beta rev 1        22/05/2016
5            3           Alpha rev 1       25/05/2016

While this is close to what I'm after, I'd rather have the titles in alphabetical order first, then all the revisions (by group) in descending date order.

I'm looking for this output:

FileID       GroupID     Title             DateAdded
-----------------------------------------------------
5            3           Alpha rev 1       25/05/2016
4            2           Beta rev 2        24/05/2016
2            2           Beta rev 1        22/05/2016
3            1           Charlie rev 2     23/05/2016
1            1           Charlie rev 1     21/05/2016

I can achieve this with two tables, but I'm ideally looking for a solution using the table I currently have.

Can anyone help with a Linq statement that will produce this output?

In short, I think what i'm asking for is to sort the table by the first most recent Title of each group (by descending date order) then by the remaining items of each group by descending date order.

Thanks in advance for your help,

Edit: to satisfy posters who want 'shown effort', I know that the following Linq statement will work to produce the first result.

var result = context.MyTable.OrderBy(x => x.GroupID)
                            .ThenByDescending(x => x.DateAdded);

As for the second result... I wouldn't be posting if I knew how to achieve it. I'm not new to SQL but I am new to this particular problem. It isn't homework and I've spent a number of hours trying to figure it out. As stated, I already have this working using two tables but it should be achievable with one.

Answer 1

In short, I think what i'm asking for is to sort the table by the first most recent Title of each group (by descending date order) then by the remaining items of each group by descending date order.

There are several way you can accomplish this in LINQ to Objects. However LINQ to Entities supports limited number of constructs, so I would suggest you using a direct translation of the above explanation:

var result = context.MyTable
    .OrderBy(t => context.MyTable
        .Where(t1 => t1.GroupId == t.GroupId)
        .OrderByDescending(t1 => t1.DateAdded)
        .Select(t1 => t1.Title)
        .FirstOrDefault())
    .ThenByDescending(t => t.DateAdded);

Answer 2

Using plain old SQL this could be a starting point:

declare @t table (FileId int, GroupId int, Title varchar(50), DateAdded datetime)
insert into @t 
    select       1, 1, 'Charlie rev 1', '2016-05-21'
    union select 2, 2, 'Beta rev 1',    '2016-05-22'
    union select 3, 1, 'Charlie rev 2', '2016-05-23'
    union select 4, 2, 'Beta rev 2',    '2016-05-24'
    union select 5, 3, 'Alpha rev 1',   '2016-05-25'

select t.*
from @t t
    join (
        select GroupId, min(Title) as mtitle from @T group by GroupId
    ) subt on t.GroupId = subt.GroupId
order by subt.mtitle, t.GroupId, t.DateAdded desc

But I can't off the top of my head write the equivalent Linq.

The plain old SQL sample works by picking one Title value (in this case the MINvalue) from a group and uses that as a representative for the whole group for sorting.