Rank one field grouping by another field

[#1]

Hello,

I have a data table which includes the following fields; SystemID, Timestamp.

I want to derive a new field in this table which ranks the Timestamp in ascending grouped by SystemID.

I'd like to do this in the script.

e.g.

SystemID,   Timestamp,   [Rank]

00001,   01/01/2013 10:00,   1

00001,   01/01/2013 12:00,   2

00001,   01/01/2013 12:30,   3

00002,   01/01/2013 11:00,   1

00002,   01/01/2013 12:12,   2

00003,   01/02/2013 10:30,   1

etc etc

Is there a function that does this or is the only option to create some code that loops through the whole recordset?

Thanks for any help.

Adam

[Oleg_Troyansky]

look into using functions peek() and previous() to build a running total that you can reset with each new SystemID.

[Gysbert_Wassenaar · Accepted solution]

Something like this:

LOAD

SystemID, Timestamp,

if(SystemID=previous(SystemID),peek('Rank')+1,1) as Rank

FROM ...mysource...

ORDER BY SystemID, Timestamp;

[#4]

Thanks,

I had to use the ORDER BY in my first table from which then I used a resident load from with the above Rank function. It wasn't working when I did the ORDER BY in the same load.

Great solution though.

Thanks again

Adam