9 Replies Latest reply: Jun 14, 2016 7:48 AM by Sunny Talwar RSS

    filtering the max amount per day in script

    Hans de Vries

      `Hi Everyone,

       

      I have a (simplified) table, in which there are more than one entries for Amount on one day.

      What I want, is just one records per Dossier#, per Date, which shows the highest value for Amount on that day. All other records should be dropped.

       

      Now I know, this should be done with max() and group by, but I can't get it to work properly.

      Any ideas?

       

      Original table:

         

      Dossier#DateAmount
      100111-2-2016€ 100,00
      100111-2-2016€ 150,00
      100114-2-2016€ 200,00
      100114-2-2016€ 350,00
      100115-2-2016€ 500,00
      10021-1-2016€ 120,00
      10022-1-2016€ 170,00
      10022-1-2016€ 190,00
      10026-1-2016€ 250,00
      10031-4-2016€ 500,00
      10036-4-2016€ 700,00

       

      Desired result:

         

      Dossier#DateAmount
      100111-2-2016€ 150,00
      100114-2-2016€ 350,00
      100115-2-2016€ 500,00
      10021-1-2016€ 120,00
      10022-1-2016€ 190,00
      10026-1-2016€ 250,00
      10031-4-2016€ 500,00
      10036-4-2016€ 700,00