select the most recent record based on record create date

[rvnth]

Hi Team, 

I aim to select the records with the latest creation date. Suppose there are six records with unique IDs but identical customer names, each with distinct creation dates. Is there a method to choose the most recent record based on the creation date?

Thank you in advance

[LakshmiPravallika · Accepted solution]

Hi @rvnth ,

Please use the query activity to select the records with the same customer name like below:

Next, Please use the Split activity to get the recent record based on creation date with Configuration shown below:

Please select "Keep only first records after sorting" option and define the descending sort as shown below

in the Next window, you can select maximum size as 1 record to get the latest one.

Sample WF Can be as shown below:

Hope this helps!

Regards,

Pravallika.

[rvnth]

Thank you @LakshmiPravallika , 

Upon attempting the solution provided earlier, I encountered the following error:
The column "tablename.REC_CREATE_DATE" is deemed invalid in the ORDER BY clause due to its absence in either an aggregate function or the GROUP BY clause.

Please suggest

[LakshmiPravallika]

Hi @rvnth ,

Please try adding the additional data in the query activity your Primary Unique Id and Creation date columns and do select group by on both the columns. Sample configuration can be as shown below:

In the Split activity, Please select the column from the added columns of query activity and do the same configuration as shown above, Change would only be in selecting the column from Additional data instead of Target Data.

Please try this and let me know if it solves the issue.

Regards,

Pravallika.

[ParthaSarathy]

Hi @rvnth ,

Utilize Deduplication activity.

After Query, use deduplication, and select

Identification of duplicates: unique_ID

Next >

Doubles to keep: 1

Method: Using an expression

Expression: select 'creation date'

and select 'keep the records with the largest value'

[rvnth]

Thank you @ParthaSarathy , this was working but I have used a different column that has unique values and can see the latest record being picked up.