Distinct ID Row Count

[Anonymous]

Hi

I would like to count the row count of the distinct value like the following table:

ID    Count

A     1

A     2

A     3
A     4    

B     1

B     2

B     3

C     1    

C     2
C     3

D     1

I have googled abt RangeCount function but I don't think it can serve my purpose.

Is there any method I can use for this calculation?

Thanks

Felix

[jaumecf23]

Hi,

Try this code:

Temp:

LOAD * INLINE [

ID  

A    

A    

A    

A      

B    

B    

B    

C      

C    

C    

D    

];

Final:

LOAD

ID,

if(RowNo()=0 or ID<>Peek(ID),1,Peek(Count)+1) as Count

Resident Temp

Order by ID;

Drop Table Temp;

[andrey_krylov]

Or like this

AutoNumber(RowNo(), ID) as Count