[SOLVED] autoincrement number function-postgres

Issue

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

Answered By – Zaynul Abadin Tuhin

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published.