Forum Discussion

pranjbhatt's avatar
pranjbhatt
New Contributor III
11 months ago

Select N largest from each group

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

    • pranjbhatt's avatar
      pranjbhatt
      New Contributor III
      11 months ago

      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