Search code examples
sqlpostgresqlduplicatesranking-functions

postgresql query how to append dups counter to name column

I have table here.

how to find all duplicate records, considering length(name)=3 ? and append dups counter to the name as a suffix?

Below screenshot, Key 6 and 7 has characters length > 3 and when length(name)=3 then Jay is there two times so Key 6 and 7 name should have dups counter as suffix , after length(name)=3. Below is expected for Key 6 and 7

enter image description here

I'm trying to use below query to get dups but it returns only dup data but not both Key 6 and 7 ,

select *, left(name,3)||rank() over(partition by left(name,3) order by key) as expected_name
 from (select key, name,rank() over(partition by left(name,3) order by key) as dups, tag
         from label where length(name)>3
      ) a 
 where dups >1

Is there a way to get both dups rows so that i can append counter using rank function.

Solution

  • To append dups counter(row number) to names having name length greater than 3

    select l.*,
Case when length(name)>3 and 
Count(*) OVER (PARTITION BY LEFT(name, 3)) > 1 
 then new_name
 else name end AS expected_name , 
new_name
from label l
left join 
(select key as key1, LEFT(name, 3) || ROW_NUMBER() OVER (PARTITION BY LEFT(name, 3)  ORDER BY key)
as new_name
from label where length(name)>3 ) name
on l.key=name.key1