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?
Advertisement
Answer
use dense_rank()
select dense_rank()over(order by id) as newid,id,persion from table_name