How to have row_number() start again in 1, once a distinct value is encountered?

Question

I have the following table in SQLite where I query using

select plain_note_id, id from attachment order by plain_note_id, id;

Here's the result.

plain_note_id   id
==================
5               1
5               2
5               8
9               3
9               5

I wish to migrate to the following new table structure. Can you suggest me several SQLite statements able to do so?

plain_note_id   id  order
========================= 
5               1   1
5               2   2
5               8   3
9               3   1
9               5   2

I guess I need to use row_number(). But, I am not sure how. I tried

select 
    plain_note_id, 
    id, 
    row_number() over (ORDER BY plain_note_id) as `order` 
from attachment 
    order by plain_note_id, id;

I am getting (Not the result I want)

plain_note_id   id  order
========================= 
5               1   1
5               2   2
5               8   3
9               3   4
9               5   5

Does anyone has any idea how I can use row_number() properly in this case?

Answer 1

In row_number() you will have to group by plain_note_id and order by id to get the desired output :

select 
    plain_note_id, 
    id, 
    row_number() over (PARTITION BY plain_note_id ORDER BY id) as `order` 
from attachment