You can use FirstSortedValue() function in script which is aggregated function
then Use Group By in Where Clause.
You have to write all fields in Group by clause which you are not using in aggregated function.
Or you can use Only() to avoid that field to write in Group By clause
Your expression like this
Thanks for this,
The group by clause; I am guessing assumes that for each client all the other columns that are being grouped by will have identical values?
My data is such that I have a client no, date column, and other fields which for each client may not be consistent.
For example lets say I have 4 entries for one client. Client no column will be the same for these 4, date column will be the same hence the max function which is fine, columns c to h may all be different for the 4 different transactions. When I do a group by using all the columns not included in the aggregation, I am still getting 4 columns back and I am assuming this is because of the differences in columns c to h?
How do I come up with one row for each client, purely based on the client no and picking up the max date, and what line corresponds with that max date?