5 Replies Latest reply: Mar 22, 2012 9:05 AM by Dai-Phuc Ha

# comparing value and taking minimum

hi everybody,

I have following issue to solve... I just want the rows with minimum time.

I have data looks like the following table:

Table1:

OrderIdTime
AA00:15
BB01:10
BB01:05
BB01:00
CC00:32
CC00:30
DD00:23
EE00:45
EE00:08

I need:

Table2:

OrderIdTime
AA00:15
BB01:00
CC00:30
DD00:23
EE00:08

I have starting write a script like this:

OrderId,

Time,

if(peek('OrderId')=OrderId, if(peek('Time)<Time, peek(Time),Time),Time) as Testing

FROM Table1;

My result is like Table1 nothin is change.

• ###### Re: comparing value and taking minimum

Hi

Use this Script get ur Result

OrderId,

Time

FROM Table1;

Left join

OrderId,

min(Time) as Testing

FROM Table1 Group by OrderId ;

Regards

Perumal A

• ###### Re: comparing value and taking minimum

Thx for quick response!

But why I still don't get my minimum value from column 'TIME'.

My basis table look like this:

OrderIDOrderDateSessionDateOrderDate-SessionDate= Diff Time
AA2011-07-15 12:07:002011-07-15 12:00:0000:07
BB2011-11-06 21:12:002011-11-06 21:00:0000:12
BB2011-11-06 21:12:002011-11-06 21:06:0000:06
BB2011-11-06 21:12:002011-11-06 21:20:00-00:08
CC2011-03-18 23:04:002011-03-18 23:00:0000:04
CC2011-03-18 23:04:002011-03-18 23:10:00-00:06
DD2011-02-08 14:08:002011-02-08 14:07:0000:01

As result I want:

OrderIdDiff Time
AA00:07
BB00:06
CC00:04
DD00:01

How I can handle the negative time value in column 'Diff Time' ????

Maybe that is my problem.

• ###### Re: comparing value and taking minimum

Hi

OrderId,

min(fabs(TimeField )) as Testing

FROM Table1 Group by OrderId ;

Try this Scipt

Regards

Perumal A

• ###### Re: comparing value and taking minimum

THX IT WORKS!!!!

• ###### Re: comparing value and taking minimum

Hia,

Try this:

table1:

OrderId,

Time

from table1;

NoConcatenate

table2: