Home / Q&A / MAX function for int field - filter for the result?
You can translate the question and the replies:

MAX function for int field - filter for the result?

is there an easy way to set a filter to only keep the max value for a field based on two other columns? is greating a group by view with the max first and joining that to the baseview the only way to accomplish this? There are two fields that make the unique identifier for the table. file-id and record-no. I only want to keep the row with that combo for this two fields with the highest version-no. what is easiest way to do it? I created a table that has the two unique id fields and the max(version-no) and was going to join it... is that right? is there a better way?
Aggregate Group By max function
user
20-08-2021 22:49:17 -0400

1 Answer

Hi, For this scenaio, I would follow the below steps: * Create a join view with the join condition set as "file-id" = "record-no" and remove the duplicate fields. * In the **Group By** tab of the view, I would enable ‘Use group by’ and add the selection fields. * In the **Output** tab, I would sort the view with the ‘order by desc’. * Next, create a **New aggr. Field** using the max function for the field "version-no" and save the view. For more information, you can take a look at the [Creating Join Views](https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/creating_derived_views/creating_join_views/creating_join_views#creating-join-views) and [MAX](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/functions/aggregation_functions/aggregation_functions#max) documentation. Additionally, if you still need help and if you are a user with valid support access then you can create a support case in [Denodo Support Site](https://support.denodo.com/) so that our support team will assist you further. Hope this helps!
Denodo Team
23-08-2021 08:42:19 -0400
You must sign in to add an answer. If you do not have an account, you can register here