Forum Discussion
How to keep rows with max value within another column value group
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.
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.