how to use max function in query activity

[Shrutii]

hi all, i have data like this,

Id	Status	Delivery Id	date
123	10	100	15-08-2023
123	10	200	16-08-2023
123	10	300	17-08-2023
123	10	400	18-08-2023
123	10	500	20-08-2023
346	10	200	18-08-2023
346	10	300	19-08-2023

and i need output like this

Id	Status	Delivery Id	date
123	10	500	20-08-2023
346	10	300	19-08-2023

so basically my requirement /conditions are

 for each id, i need max of delivery id and last modified date should be on and after 15-08-2023 and should be max of last modified date

i need to club all these conditions in adobe campaign query activity

please help me

how can i set query actiivty for this

please help me here, tried multiple hit and try but no luck\

and

[ParthaSarathy · Accepted solution]

Hi @Shrutii ,

You can achieve your use case using Query and deduplication activity.

First in Query, create a filtering condition as DATE on or after 15-08-2023 00:00:00 (select date from drop-down calendar)

Next in Deduplication activity > edit configuration > targeting dimension: temporary schema >next> select '@id' in identification of duplicate >next>

Duplication method:

Doubles to keep: 1

Method: using an expression

Expression: select 'Delivery ID'

And select 'keep the records with the largest value'

Run the workflow.

Now the output of Deduplication activity will satisfy your requirement.