Simon Simon - 15 days ago 5
SQL Question

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

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

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