Get min value in distinct load

[Anonymous]

Hi,

I have a table with data like this:

id	time_stamp	other_time_stamp
1	2017-09-11 12:30	2017-09-11 12:00
1	2017-09-11 12:30	2017-09-11 12:00
1	2017-09-11 12:30	2017-09-11 12:00
1	2017-09-11 12:30	2017-09-11 12:20
1	2017-09-11 12:30	2017-09-11 12:20
2	2017-09-11 13:30	2017-09-11 13:10
2	2017-09-11 13:30	2017-09-11 13:10
2	2017-09-11 13:30	2017-09-11 13:10
2	2017-09-11 13:30	2017-09-11 13:15
2	2017-09-11 13:30	2017-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:

id	time_stamp	other_time_stamp
1	2017-09-11 12:30	2017-09-11 12:00
1	2017-09-11 12:30	2017-09-11 12:20
2	2017-09-11 13:30	2017-09-11 13:10
2	2017-09-11 13:30	2017-09-11 13:15

Now I would like to get:

id	time_stamp	other_time_stamp
1	2017-09-11 12:30	2017-09-11 12:00
2	2017-09-11 13:30	2017-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.

[Anil_Babu_Samineni · Accepted solution]

Try this?

[my_table]:

Load Distinct

id,

time_stamp,

other_time_stamp

resident my_old_table;

Drop Table my_old_table;

Right Join (my_table)

Load id, min(other_time_stamp) as other_time_stamp Resident my_table Group By id;

Note - Please play with Date format of Timestamp