autoincrement number function-postgres

Question

i have a table like this:

id	person
20	adams
20	george
40	jina
46	rico
80	naya
90	john
90	peter
90	richard

i want to find a way to select a new_id starting from 1 and increazing +1 every time id is different. for example i want a select with a result like this:

new_id	id	person
1	20	adams
1	20	george
2	40	jina
3	46	rico
4	80	naya
5	90	john
5	90	peter
5	90	richard

is there any function in postgres doing something like that?

Answer 1

use dense_rank()

select dense_rank()over(order by id) as newid,id,persion
from table_name

demo link