Order by descending group by Max date, then order by single row in the group by date ascending using linq Method sintax

c# entity-framework-6 linq sql sql-server

Question

I have a table like this

Id      Date       GroupId   Text    Column1   Column2 ...
1    2020-02-02       1      ....     ....       ...
2    2020-02-04       1      ....     ....       ...
3    2020-02-03       1      ....     ....       ...
4    2020-02-02       2      ....     ....       ...
5    2020-02-05       2      ....     ....       ...

I need to obtain this result:

Id      Date       GroupId   Text    Column1   Column2 ...
5    2020-02-05       2      ....     ....       ...
4    2020-02-02       2      ....     ....       ...
1    2020-02-02       1      ....     ....       ...
3    2020-02-03       1      ....     ....       ...
2    2020-02-04       1      ....     ....       ...

I explain I need to get before all rows of the group 2 because the max date is in the group 2... I need to order the group by date descending but every single group should be ordered by date ascending.

I find difficult to write it in sql too. Can anyone help me please? Thank you

EDIT: Here what I have tried to do:

var result = messages.Select(m => new MyViewModel()
            {
                Id = m.Id,
                Date = m.Date,
                Text = m.Text,                    
                GroupId = m.GroupId
            }).GroupBy(d => d.GroupId)
              .SelectMany(g => g)
              .OrderByDescending(x => x.GroupId)
              .ThenBy(x => x.Date);

But it does not work

1
0
2/4/2020 5:14:02 PM

Accepted Answer

I suggest creating an intermediate result having a GroupDate property. In my example I am using C# 7.0 Tuples. You could use anonymous types as well, if you are using an older C# version.

var result = messages
    .GroupBy(m => m.GroupId)                                // Create Tuple ...
    .Select(g => (Group: g, GroupDate: g.Max(m => m.Date))) // ... with group and group date.
    .SelectMany(a =>              // Expand the group
        a.Group.Select(m =>       // Create new tuple with group date and model.
            (a.GroupDate,
             Model: new MyViewModel() {
                 Id = m.Id,
                 Date = m.Date,
                 Text = m.Text,
                 GroupId = m.GroupId
             })))
    .OrderByDescending(x => x.GroupDate)
    .ThenBy(x => x.Model.Date)
    .Select(x => x.Model);         // Extract the view model from the tuple.

Result:

Id = 2, Date = 2020-02-02, GroupId = 2
Id = 2, Date = 2020-02-05, GroupId = 2
Id = 1, Date = 2020-02-02, GroupId = 1
Id = 1, Date = 2020-02-03, GroupId = 1
Id = 1, Date = 2020-02-04, GroupId = 1

Example for tuple:

var t = (X: 15, Name: "axis");
Print($"X = {t.X}, Name = {t.Name}");

The name of a tuple property can also be inferred like in (a.GroupDate, Model: ...). The first property will automatically be called GroupDate. The second is named explicitly Model.

1
2/4/2020 5:09:34 PM

Popular Answer

In SQL, you can use window functions:

order by 
    max(date) over(partition by groupId),
    case when max(date) over(partition by groupId) = max(date) over() then date end desc,
    date


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow