select the most recent record based on record create date

Forum|Forum|1 year ago
May 16, 2024
2 replies
1037 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by LakshmiPravallika

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.

LakshmiPravallika

Accepted solution

Community Advisor

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.

R

rvnthAuthor

Level 5

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

Community Advisor

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.