cancel
Showing results for 
Search instead for 
Did you mean: 

Select N largest from each group

pranjbhatt
New Contributor III

Hi, 

I am building a pipeline to select latest datetime from the data file. I also need the latest value for all source_code (essentially a groupby and then max value of within group). I tried Aggregate function to select MAX over datetime and in the GROUPBY field I added source_code but do not receive the intendent result.

Is there other way ? I have sorted the columns I need to find MAX value.  

2 REPLIES 2

koryknick
Employee
Employee

@pranjbhatt - can you provide a small sample of input data and desired output to clarify your question?

pranjbhatt
New Contributor III

Hi, 

Following is the sample data. I need to have latest last_date values for each of the source (source_code) and each of the target table.  Note that date is in dd-MM-yyyy format.

 

INPUT     
TARGET TABLESOURCE CODELAST DATE RUN DATELOAD STATUS TYPE
DIM_CLIENTconcentrix03-09-202403-09-2024NowPositive
DIM_CLIENTconcentrix30-08-202403-09-2024NowPositive
DIM_CLIENTenvizi01-09-202403-09-2024NowPositive
DIM_CLIENTenvizi27-08-202403-09-2024NowPositive
USERenvizi28-08-202403-09-2024NowPositive
USERconcentrix02-09-202403-09-2024NowPositive
USERconcentrix29-08-202403-09-2024NowPositive
USER envizi27-08-202403-09-2024NowPositive

 

OUTPUT     
TARGET TABLESOURCE CODELAST DATE RUN DATELOAD STATUS TYPE
DIM_CLIENTconcentrix03-09-202403-09-2024NowPositive
DIM_CLIENTenvizi01-09-202403-09-2024NowPositive
USERenvizi28-08-202403-09-2024NowPositive
USERconcentrix02-09-202403-09-2024NowPositive