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.}
}
ds[GroupBy[#ID &]][All, Last]
, whereds
is your data set. Generally, it is better to provide explicit small sample in your question, to make it self-contained. $\endgroup$