2 Replies Latest reply: Dec 20, 2015 12:00 AM by santosh Roy RSS

    how get max date records in script...???

    santosh Roy



      issue : I have tried to get record in script from table based on max date but unable to get it.

      background : I have price amendment table, which contents item wise date wise price change history for all the items.

      example : item xyz may have amend 10 times, so table will have date wise 10 records along with some other information.

      i want : i wanted to pick the max date  or latest record for the item along with other details

      what i tried : i have tried group by but unable to get desired output


      my data base :

      i wanted to have record which is highlighted in yellow colour  (i.e  wanted to have record which has max amendment date along with other details)

      (constraint : no aggregation for rate column)


      i used following code but no desired out put



      distinct (item & vendor & plant) as item_vendor_plant_key,


      max(date (amendment date)),

      rate          //----i donot want to use aggregation here nor i wanted to put it in group by---

      resident poam_table

      group by (item & vendor & plant) ;

        • Re: how get max date records in script...???
          Sunny Talwar

          Try this:


          LOAD Distinct (item & vendor & plant) as item_vendor_plant_key,

                    FirstSortedValue(amendment, -[amendment date]) as MaxDateAmendment,

                    Date(Max([amendment date])) as MaxAmendmentDate,

                    FirstSortedValue(rate, -[amendment date]) as MaxDateRate

          Resident poam_table

          Group By item, vendor, plant;

          UPDATE: Missed the negative sign next to amendment date in the first sorted value

            • Re: how get max date records in script...???
              santosh Roy

              Excellent solution,


              it worked and produced desirable output for me. Many many thanks buddy,


              Mean while i have  found alternate solution for the same (it is quite lengthy but works good too)


              step 1st : Create key in poam_table (item_vendor_plant_amd_key)


              step 2nd:

              left join (poam_table)


              Distinct item & vendor & plant & max(amendment) as item_vendor_plant_amd_key,

              'max_amendment_no' as max_amdendment_flag

              resident poam_table;

              step 3rd:





              resident poam_table

              where max_amdendment_flag='max_amendment_no';

              drop table poam_table;

              but once again many thanks to you...you saved my time.....