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