Forum Discussion

Frequent Visitor

2 years ago

Solved

How to keep rows with max value within another column value group

I have a dataset file id value 2023-07-31_wert.csv 555 45 2023-07-27_wert.csv 555 40 2023-07-15_wert.csv 555 35 2023-07-30_uiop.csv 444 35 2023-07-10_uiop.csv 444 30...

Need Help

ToddChitt
2 years ago
So from your example for Id = 555, the winner (row to keep) is the first one because it has the max value, but you also want to include the details (like the file colum) from that row.
Question: Can you be sure that each ID will have ONE and ONLY ONE MAX(VALUE)?

If so, try this:
DUPLICATE the data set.
On the duplicate, do a GROUP BY on Id column, and take an aggregate of MAX of the Value column.
Now JOIN this table with the original table. Do an INNER JOIN so that you only get matching records. Match on the ID and MAX(Value) = Value.

G-newbie

New Member

2 years ago

I had the same issue, but with different versions of a document and only wanted the line items from the latest version.

I grouped by maximum version number as below

Then expanded the new details column (Table).

This creates a duplicate line item for each version.

I found the original version column in the expanded table then added conditional column (I called mine Keep_Remove). If [MaxV] column = [Version_No] column then keep else remove.

Filter out "remove" from the new column

Hope this helps.