Number rows grouped by user_id

[#1]

Hi,

I´m facing a Problem:

Let´s say I have a Table like:

id	user_id	date
1	1	2015-01-01
2	2	2015-01-01
3	1	2015-01-02
4	1	2015-01-03
5	2	2015-01-03
6	3	2015-01-01

What i want to achieve:

I want to number the rows grouped by the field "user_id". the row with the lowest date should be "1", the row with the second lowest date should be "2", and so on.

Result should be like:

id	user_id	date	Number
1	1	2015-01-01	1
2	2	2015-01-01	1
3	1	2015-01-02	2
4	1	2015-01-03	3
5	2	2015-01-03	2
6	3	2015-01-01	1

Can somebody please help me. I couldn´t find any solution.

Thank you

[Gysbert_Wassenaar · Accepted solution]

Like this:

MyTable:

LOAD

     id,

     user_id,

     date,

     autonumber(RecNo(),user_id) as Number

FROM ...mysource...;

[pokassov]

Hello!

Can you change your script to achieve your goal?

If the answer is 'Yes' then:

t2:

noconcatenate

load id, user_id, date,

if(previous(user_id)<>user_id,1,peek('number_user')+1) as number_user

resident your_table

order by user_id, date

left join (your_table)

load id, nu,ber_user

resident t2;

drop table t2;

[#4]

wow, thank you!