Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
OrderId | Time |
---|---|
AA | 00:15 |
BB | 01:10 |
BB | 01:05 |
BB | 01:00 |
CC | 00:32 |
CC | 00:30 |
DD | 00:23 |
EE | 00:45 |
EE | 00:08 |
I need:
Table2:
OrderId | Time |
---|---|
AA | 00:15 |
BB | 01:00 |
CC | 00:30 |
DD | 00:23 |
EE | 00:08 |
I have starting write a script like this:
LOAD
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.
Thx in advance!!!
Hi
LOAD
OrderId,
min(fabs(TimeField )) as Testing
FROM Table1 Group by OrderId ;
Try this Scipt
Regards
Perumal A
Hi
Use this Script get ur Result
LOAD
OrderId,
Time
FROM Table1;
Left join
LOAD
OrderId,
min(Time) as Testing
FROM Table1 Group by OrderId ;
Regards
Perumal A
Hia,
Try this:
table1:
LOAD
OrderId,
Time
from table1;
NoConcatenate
table2:
load
OrderId,
min(0+Time) as Time
Resident table1
Group By OrderId;
drop Table table1;
Thx for quick response!
But why I still don't get my minimum value from column 'TIME'.
My basis table look like this:
OrderID | OrderDate | SessionDate | OrderDate-SessionDate= Diff Time |
---|---|---|---|
AA | 2011-07-15 12:07:00 | 2011-07-15 12:00:00 | 00:07 |
BB | 2011-11-06 21:12:00 | 2011-11-06 21:00:00 | 00:12 |
BB | 2011-11-06 21:12:00 | 2011-11-06 21:06:00 | 00:06 |
BB | 2011-11-06 21:12:00 | 2011-11-06 21:20:00 | -00:08 |
CC | 2011-03-18 23:04:00 | 2011-03-18 23:00:00 | 00:04 |
CC | 2011-03-18 23:04:00 | 2011-03-18 23:10:00 | -00:06 |
DD | 2011-02-08 14:08:00 | 2011-02-08 14:07:00 | 00:01 |
As result I want:
OrderId | Diff Time |
---|---|
AA | 00:07 |
BB | 00:06 |
CC | 00:04 |
DD | 00:01 |
How I can handle the negative time value in column 'Diff Time' ????
Maybe that is my problem.
Hi
LOAD
OrderId,
min(fabs(TimeField )) as Testing
FROM Table1 Group by OrderId ;
Try this Scipt
Regards
Perumal A
THX IT WORKS!!!!