Get only rows with the latest date for each name

Question

I'm trying to write a query that returns only those rows that contain the latest date for each name.

So for example, this data:

Name	Date Sold	More Columns...
Bob	2021-01-05
Mike	2021-01-18
Susan	2021-01-23
Bob	2021-02-04
Susan	2021-02-16
Mike	2021-03-02

Would produce this result:

Name	Date Sold	More Columns...
Bob	2021-02-04
Susan	2021-02-16
Mike	2021-03-02

It's sort of like a GROUP BY, but I'm not aggregating anything. I only want to filter the original rows.

How could I write such a query?

NOTE: In the end, this will be a SQL Server query but I need to write it using Entity Framework.

UPDATE: In reality, this is part of a much more complex query. It would be extremely difficult for me to implement this as a raw SQL query. If at all possible, I need to implement using Entity Framework.

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — SMor, Oct 18 '21 at 17:36
@SMor: Looks similar but I don't think that approach is even possible in Entity Framework. — Jonathan Wood, Oct 18 '21 at 17:38
Perhaps place the row-numbering in a view, then select from that in EF — Charlieface, Oct 18 '21 at 22:32

score 2 · Answer 1 · answered Oct 18 '21 at 17:37

2

Two options

Select top 1 with ties *
 From  YourTable 
 Order by row_number() over (partition by Name order by Sold_Date desc)

or slightly more performant

with cte as (
Select *
      ,RN = row_number() over (partition by Name order by Sold_Date desc)
 From  YourTable
)
Select *
 From  cte 
 Where RN=1

answered Oct 18 '21 at 17:37

John Cappelletti

79,615
7
44
66

Thanks, but what is `ties *` in your first example? Also, do I assume you're a SQL Server guy, and not an Entity Framework guy? – Jonathan Wood Oct 18 '21 at 17:39
@JonathanWood You are correct... skipped right over the EF. – John Cappelletti Oct 18 '21 at 17:40

Milad Dastan Zand · Answer 2 · 2021-10-18T17:38:08.203

1

try this

;with Groups as
(
    Select [Name], max([Date Sold]) as [Date Sold]
    From Table
    Group By [Name]
)
Select Table.* From Groups
Inner Join Table on Table.[Name] = Groups.Name And Table.[Date Sold] = Groups.[Date Sold]

edited Oct 18 '21 at 17:38

answered Oct 18 '21 at 17:25

Milad Dastan Zand

1,062
1
10
21

But, as explained, I will need all columns in the row. – Jonathan Wood Oct 18 '21 at 17:31
oh I see now, I will change my answer soon @JonathanWood – Milad Dastan Zand Oct 18 '21 at 17:34
@JonathanWood I edit my answer now you can get all columns with that filter – Milad Dastan Zand Oct 18 '21 at 17:38

Amy B · Accepted Answer · 2021-10-18T18:13:30.640

Adapted from Error while flattening the IQueryable<T> after GroupBy()

var names = _context.Items.Select(row => row.Name).Distinct();
var items =
  from name in names
  from item in _context.Items
    .Where(row => row.Name == name)
    .OrderByDescending(row => row.DateSold)
    .Take(1)
  select item;

var results = items.ToArrayAsync();

Let's break this down:

A query expression which establishes the keys for our next query. Will eventually be run as a subquery.

var names = _context.Items.Select(row => row.Name).Distinct();

Another query, starting with the keys...

var items =
  from name in names

... and for each key, let's find the matching row ...

  from item in _context.Items
    .Where(row => row.Name == name)
    .OrderByDescending(row => row.DateSold)
    .Take(1)

... and we want that row.

  select item;

Run the combined query.

var results = items.ToArrayAsync();

I'm going to need to reread that a few dozen times until it makes sense. — Jonathan Wood, Oct 18 '21 at 18:04

Get only rows with the latest date for each name

3 Answers3