How to assign unique value for each group in other column-CodePudding

I have table data like below. I want unique values for each group by entity column.

I tried row_number and rank window function but they are not giving desired result.

Expected outcome ID column is

Is it possible in PostgreSQL to get unique number for each group by entity column data?

CodePudding user response：

I believe you are looking for DENSE_RANK()

with your_table(entity, attribute) as (
  select 'a', 'Car' 
  union all select 'a', 'Car' 
  union all select 'a', 'Bus' 
  union all select 'b', 'Car' 
  union all select 'b', 'Cycle' 
  union all select 'C', 'Car'
  union all select 'D', 'Bus' 
  union all select 'D', 'Motocyle(?)' 
  union all select 'D', 'Car' 
  union all select 'a', 'Cycle' 
  
)
select 
  dense_rank() over (order by entity) as ID, 
  entity, 
  attribute 
from your_table

dbfiddle