Select Latest Item for Each Type

Question

This is based on a previous question by Todilo.

The following accepted answer works great except I am required to return all records where type is null in addition to the latest for each type:

var query = Posts.GroupBy(p => p.Type)
              .Select(g => g.OrderByDescending(p => p.Date)
                            .FirstOrDefault()
               )

The scenario is as follows:

+----+--------------------------+-------+------------+
| id |         content          | type  |    date    |
+----+--------------------------+-------+------------+
|  0 | Some text                | TypeA | 2013-04-01 |
|  1 | Some older text          | TypeA | 2012-03-01 |
|  2 | Some even older texttext | TypeA | 2011-01-01 |
|  3 | Sample                   |       | 2013-02-24 |
|  4 | A dog                    | TypeB | 2013-04-01 |
|  5 | And older dog            | TypeB | 2012-03-01 |
|  6 | An even older dog        | TypeB | 2011-01-01 |
|  7 | Another sample           |       | 2014-03-06 |
|  8 | Test                     |       | 2015-11-08 |
+----+--------------------------+-------+------------+

The result should be

Some text      | TypeA
Sample         |
A dog          | TypeB
Another sample |
Test           |

I can only think of this as a LINQ union. – code4life Sep 08 '15 at 21:45 — code4life, Sep 08 '15 at 21:45

score 2 · Accepted Answer · answered Sep 08 '15 at 17:24

What about that:

var query = Posts
              .GroupBy(p => p.Type)
              .Select(g => g.OrderByDescending(p => p.Date).FirstOrDefault()).ToList()
var lostNullItems = Posts.Where(p => p.Type == null && !query.Contains(p));
var newQuery = query.Union(lostNullItems);

If you don't need the order of the items you can use:

var query = Posts
              .GroupBy(p => p.Type)
              .SelectMany(g => 
                   {
                      var result = g.OrderByDescending(p => p.Date);
                      return g.Key == null ? result ? Enumerable.Repeat(result.First(), 1);
                   });

This code is not tested.

Thanks for the answer. The first method works. I am going to try the second one later. — corix010, Sep 09 '15 at 16:16

score 1 · Answer 2 · answered Sep 08 '15 at 17:56

Try code below. Because of the grouping the order isn't the same

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("content", typeof(string));
            dt.Columns.Add("type", typeof(string));
            dt.Columns["type"].AllowDBNull = true;
            dt.Columns.Add("date", typeof(DateTime));

            dt.Rows.Add(new object[] { 0, "Some text", "TypeA", DateTime.Parse("2013-04-01")});
            dt.Rows.Add(new object[] { 1, "Some older text", "TypeA", DateTime.Parse("2012-03-01")});
            dt.Rows.Add(new object[] { 2, "Some older texttext", "TypeA", DateTime.Parse("2011-01-01")});
            dt.Rows.Add(new object[] { 3, "Sample", null, DateTime.Parse("2013-02-24")});
            dt.Rows.Add(new object[] { 3, "A dog", "TypeB", DateTime.Parse("2013-04-01")});
            dt.Rows.Add(new object[] { 4, "And older dog", "TypeB", DateTime.Parse("2012-03-01")});
            dt.Rows.Add(new object[] { 5, "An even older dog", "TypeB", DateTime.Parse("2011-01-01")});
            dt.Rows.Add(new object[] { 4, "Another sample", null, DateTime.Parse("2014-03-06")});
            dt.Rows.Add(new object[] { 5, "Test", null, DateTime.Parse("2015-11-08")});

            var results = dt.AsEnumerable()
                .GroupBy(x => x.Field<string>("type"))
                .Select(x => x.Key == null ? x.ToList() : x.Select(y => new {date = y.Field<DateTime>("date"), row = y}).OrderByDescending(z => z.date).Select(a => a.row).Take(1))
                .SelectMany(b => b).Select(c => new {
                    content = c.Field<string>("content"), 
                    type = c.Field<string>("type") 
                }).ToList();


        }
    }
}

Thanks for the answer. I am going to try your solution later. — corix010, Sep 09 '15 at 16:17

code4life · Answer 3 · 2015-09-09T10:28:54.983

1

I'm thinking that a LINQ union is the only way to do it, primarily because of how you are sorting the output by date.

The first query should look like this:

var nullTypes = from p in Posts
                     where p.Type == null
                     select p;

The primary query should just filter out the nulls:

var query = Posts.Where(p => p.Type != null).GroupBy(p => p.Type)
          .Select(g => g.OrderByDescending(p => p.Date)
                        .FirstOrDefault()
           )

Union to your primary query:

var unionQuery = query.Union(nullTypes).OrderBy(p => p.Date);

The output will match what you are expecting, only that the first two lines will be inverted in terms of order:

Sample         |
Some text      | TypeA
A dog          | TypeB
Another sample |
Test           |

This is because the line item containing "Sample" has an older date than "Some text".

You shouldn't need to do any SelectMany - the original Select will projects a single item, so this works out into an IEnumerable that can fit the union.

edited Sep 09 '15 at 10:28

answered Sep 08 '15 at 22:01

code4life

15,655
7
50
82

The union will add `sample` two times. The first one out of `query` and the second one out of `nullTypes`. Your post is exaclty my answer. Do you piont to my answer with `SelectMany`? Using `SelectMany` let you bring the whole request into one query. In that case you don't need any `Union`. – Sebastian Schumann Sep 09 '15 at 08:00
@Verarind: Yup, I missed that. The original query should have nulls filtered out or else duplications will occur. – code4life Sep 09 '15 at 10:29
@code4life Thanks for your answer. I am going to try your solution later. – corix010 Sep 09 '15 at 16:18

Select Latest Item for Each Type

3 Answers3