Assign row no

[#1]

Hi there,

If I had a table of data like the below, is it possible to have 3 column generated where by it increments by 1 each time the record in A is found?

So A can occur one or more times but B is unique.

Hope this makes sens.

Thanks

Aidan

A               B               C

test1         apple          1

test1         orange        2

test2         apple          1

test3         apple          1

test3         pear            2

[MarcoWedel]

Hi,

just tested with 100000 rows of numerical values.

The solution Manish Kachhia provided works quite well:

table1:
LOAD Ceil(Rand()*10000) as A,
    Ceil(Rand()*10000) as B
AutoGenerate 100000;

table2:
LOAD *,
    AutoNumber(B, A) as C
Resident table1
Order by A, B;

DROP Table table1;

hope this helps

regards

Marco

[MarcoWedel]

if the B-Value really doesn't matter, then

AutoNumber(RecNo(), A) as C

could be also a solution.

regards

Marco

[#4]

Thanks you kindly

[MK_QSL]

LOL !

Still my answer is not even correct or helpful ! 🙂

[MarcoWedel]

Aidan,

please mark Manish Kachhia's answer as correct instead of mine. I just tested it for you.

thanks

regards

Marco

[MK_QSL]

No Marco, It's OK dear, I am just kidding... Doesn't matter...