2nd Value Group By

[#1]

Hi All,

I am battling to figure this out. Below is my sample data

Data:

LOAD * INLINE [

ID, HistoryID, TimeStamp

1,1,02/08/2014 14:00:00

1,2,02/08/2014 14:26:00

1,3,02/08/2014 14:56:00

2,4,04/08/2014 11:05:00

2,5,04/08/2014 12:15:00

3,6,06/08/2014 13:00:00

3,7,06/08/2014 13:50:00

3,8,07/08/2014 14:00:00

3,9,07/08/2014 14:26:00

What I want is a table that has the 2nd value for each grouped ID added as a new column

i.e.

ID, HistoryID, TimeStamp, 2ndValue

1,1,02/08/2014 14:00:00,02/08/2014 14:26:00

1,2,02/08/2014 14:26:00,02/08/2014 14:26:00

1,3,02/08/2014 14:56:00,02/08/2014 14:26:00

2,4,04/08/2014 11:05:00,04/08/2014 12:15:00

2,5,04/08/2014 12:15:00,04/08/2014 12:15:00

3,6,06/08/2014 13:00:00,06/08/2014 13:50:00

3,7,06/08/2014 13:50:00,06/08/2014 13:50:00

3,8,07/08/2014 14:00:00,06/08/2014 13:50:00

3,9,07/08/2014 14:26:00,06/08/2014 13:50:00

How do I do this in a simple way.

Please urgently need your help figuring this one out

Cheers,

Byron

[datanibbler]

Hi,

you need to build a loop over the group_IDs and then use the max([field], 1) function - is the first thing that comes to my mind. There might be an easier way or you might be able to do it in one pass - but I think that one of the main objectives should always be that others with no more than general IT knowledge should be able to understand your code - so I'd rather do a loop here than a "complicated" construct with WHERE() and GROUP BY and whatnot...

HTH

Best regards,

DataNibbler

[martinpohl · Accepted solution]

Here the script:

Data:

LOAD * INLINE [

ID, HistoryID, TimeStamp

1,1,02/08/2014 14:00:00

1,2,02/08/2014 14:26:00

1,3,02/08/2014 14:56:00

2,4,04/08/2014 11:05:00

2,5,04/08/2014 12:15:00

3,6,06/08/2014 13:00:00

3,7,06/08/2014 13:50:00

3,8,07/08/2014 14:00:00

3,9,07/08/2014 14:26:00

];

left join

load ID,

FirstSortedValue(TimeStamp,HistoryID,2) as SecondTimeStamp

resident Data

group by ID

;

See also attached

Regards

[sujeetsingh]

You can go with either iteration or the group by function.

[datanibbler]

Hi Martin,

that is pretty easy - and straightforward to understand. I'd recommend that. Didn't know yet that FirstSortedValue could also return the second value in a group ...

Again what learned 😉

[#6]

Agree with Nibbler! Didn't know firstsorted value had a rank parameter. Awesome and clean solution

Thank you,

Byron

[#7]

Hi Byron, You can Try with Min function like below:

Min(Timestamp,2)