Select last occurence of key in dataset

Question

I have a large Dataset filled with data from a SQL DB. I sort the set via name and date with the SelectBy function and get something like this:

In the end, I want a reduced Dataset with only one entry (with the youngest SQLDateTime) per "ID" (first column). My question is now, how can I select the last occurrence of each "ID" listed in the first row?

Sample Data

$dataset = Dataset @* Map[AssociationThread[{"ID", "Date", "Value1", "Value2"} -> #]&] @
  { {"CAV00009", SQLDateTime[{2012, 12, 11, 0, 0, 0.}], 1., 31.}
  , {"CAV00009", SQLDateTime[{2013, 1, 22, 0, 0, 0.}], 1., 139.}
  , {"CAV00009", SQLDateTime[{2013, 2, 19, 19, 30, 0.}], Null, 720.}
  , {"CAV00009", SQLDateTime[{2013, 3, 8, 19, 40, 0.}], Null, 720.}
  , {"CAV00009", SQLDateTime[{2013, 4, 2, 21, 50, 0.}], Null, 720.}
  , {"CAV00010", SQLDateTime[{2012, 12, 12, 0, 2, 0.}], 1., 30.}
  , {"CAV00010", SQLDateTime[{2013, 1, 28, 9, 25, 0.}], 1., 123.9}
  , {"CAV00010", SQLDateTime[{2013, 3, 21, 20, 0, 0.}], Null, 720.}
  , {"CAV00010", SQLDateTime[{2013, 5, 24, 19, 45, 0.}], Null, 786.}
  , {"CAV00010", SQLDateTime[{2013, 6, 1, 17, 0, 0.}], Null, 786.2}
  , {"CAV00011", SQLDateTime[{2012, 12, 10, 10, 0, 0.}], 1., 30.}
  , {"CAV00011", SQLDateTime[{2013, 1, 23, 20, 50, 0.}], 1., 139.}
  , {"CAV00011", SQLDateTime[{2013, 1, 25, 21, 0, 0.}], 2, 720.}
  , {"CAV00011", SQLDateTime[{2013, 4, 24, 20, 35, 0.}], Null, 732.}
  , {"CAV00011", SQLDateTime[{2013, 5, 4, 22, 0, 0.}], Null, 786.}
  , {"CAV00011", SQLDateTime[{2013, 8, 27, 6, 40, 0.}], Null, 720.}
}

Answer 1

This will do the trick:

$dataset[GroupBy[#ID &] /* Values, Last]

To see why, let's build it up step-by-step. First, we will use GroupBy to gather together all of the rows for each ID:

$dataset[GroupBy[#ID &]]

Next, we will extract the last element from each group:

$dataset[GroupBy[#ID &], Last]

This is almost our desired result, except that we still have the lingering grouping keys from the first step. We remove them using Values:

$dataset[GroupBy[#ID &] /* Values, Last]

This is our final result.

What if the original data were unsorted?

If we had no guarantee that the original data was presented in sorted order, we can still find the latest row in each group using MaximalBy:

$dataset[GroupBy[#ID &] /* Values, MaximalBy[#Date &] /* First]

Note the use of First in the subexpression MaximalBy[...] /* First. This is necessary because MaximalBy always returns a list of all elements that are maximal. The use of First is arbitrary, and could just as easily be Last. It only makes a difference if there a multiple maximal entries. If multiple maxima are a possibility, and we care about which one we select, then we should consider expanding the list of inspected components to reflect our preference (e.g. MaximalBy[{#Date, #Value1, #Value2}&]).