5 Replies Latest reply: Jun 2, 2016 5:43 PM by Ramon Covarrubias

# Give each row a sequential number depending on several fields

Hi everyone,

I'm looking to number my rows, based on values of three fields.

The data looks like the following: I have UserId, VisitId, and HitNumber. However, HitNumber isn't always sequential, and I'd like to have another field that does give each row a sequential number, but only within the confines of UserId and VisitId.

I want to create the field PVNumber, that will give each UserId + VisitId + HitNumber combination a sequential number as shown below:

UserIdVisitIdHitNumberPVNumber
1234567987611
1234567987632
1234567987643
1234567987664
4567890765411
4567890765452
4567890765463

How can I do this?

• ###### Re: Give each row a sequential number depending on several fields

May be this:

AutoNumber(RowNo(), UserId&VisitId) as PVNumber

• ###### Re: Give each row a sequential number depending on several fields

Table:

VisitId,

HitNumber

FROM

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

AutoNumber(RowNo(), UserId&VisitId) as PVNumber

Resident Table

Order By UserId, VisitId, HitNumber;

DROP Table Table;

• ###### Re: Give each row a sequential number depending on several fields

Thanks...however, this is incredibly slow. I'm running the script on 5 million rows, and it's taken almost 17 hours so far to go through 1.8 million rows. Is there a way to speed this method up?

• ###### Re: Give each row a sequential number depending on several fields

If the data is already sorted, you can try it without the resident load. The reason for resident was to sort the data which might be slowing down for you

Table:

VisitId,

HitNumber,

AutoNumber(RowNo(), UserId&VisitId) as PVNumber

FROM

(html, codepage is 1252, embedded labels, table is @1);

• ###### Re: Give each row a sequential number depending on several fields

one option is using peek

Table:

VisitId,

HitNumber

FROM

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

if(peek(UserId) = UserId and peek(VisitId) = VisitId, rangesum(peek(PVNumber),1),1) as PVNumber

Resident Table

Order By UserId, VisitId, HitNumber;

DROP Table Table;

if you want to still use and autonumber like function then try using autonumberhash128 instead, I have seen it being faster

Table:

VisitId,

HitNumber

FROM

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

autonumberhash128(RowNo(), UserId,VisitId) as PVNumber

Resident Table

Order By UserId, VisitId, HitNumber;

DROP Table Table