Search code examples
sqlpostgresqlfunctionauto-increment

autoincrement number function-postgres

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?

Solution

  • use dense_rank()

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

    demo link