1 Reply Latest reply: Nov 9, 2017 3:18 AM by Anil Babu RSS

    Get min value in distinct load

    Marko Zadravec

      Hi,

      I have a table with data like this:

       

      idtime_stampother_time_stamp
      12017-09-11 12:302017-09-11 12:00
      12017-09-11 12:302017-09-11 12:00
      12017-09-11 12:302017-09-11 12:00
      12017-09-11 12:302017-09-11 12:20
      12017-09-11 12:302017-09-11 12:20
      22017-09-11 13:302017-09-11 13:10
      22017-09-11 13:302017-09-11 13:10
      22017-09-11 13:302017-09-11 13:10
      22017-09-11 13:302017-09-11 13:15
      22017-09-11 13:302017-09-11 13:15

       

      If I make a load

       

          [my_table]:

          Load Distinct

                id,

                time_stamp,

                other_time_stamp

       

              resident my_old_table;

       

          Drop Table my_old_table;


      What I get is:

       

      idtime_stampother_time_stamp
      12017-09-11 12:302017-09-11 12:00
      12017-09-11 12:302017-09-11 12:20
      22017-09-11 13:302017-09-11 13:10
      22017-09-11 13:302017-09-11 13:15

       

      Now I would like to get:

       

      idtime_stampother_time_stamp
      12017-09-11 12:302017-09-11 12:00
      22017-09-11 13:302017-09-11 13:10

       

      So I try:

       

          [my_table]:

          Load Distinct

                id,

                time_stamp,

                min(other_time_stamp)

       

              resident my_old_table;

       

          Drop Table my_old_table;



      But I get error when loading...

       

      The following error occurred:

      Invalid expression

       

       

      I even try some other solutions from forums, like:

       

          [my_table]:

          Load Distinct

                id,

                time_stamp,

               other_time_stamp

       

              resident my_old_table

              where id<>peek(id) order by id, other_time_stamp;

       

          Drop Table my_old_table;

       

       

      But I also get same error.