0

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.

Hari
  • 299
  • 4
  • 12
  • 1
    I'm not 100% sure I'm following, but something like `CASE WHEN Count(*) OVER (PARTITION BY LEFT(name, 3)) > 1 AND ROW_NUMBER() OVER (PARTITION BY LEFT(name, 3) ORDER BY key) > 1 THEN LEFT(name, 3) || ROW_NUMBER() OVER (PARTITION BY LEFT(name, 3) ORDER BY key) -1 ELSE name END AS name, `? – JNevill Mar 10 '22 at 19:58
  • @JNevill thanks . helpful to get expected results – – Hari Mar 12 '22 at 18:36

1 Answers1

0

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
Hari
  • 299
  • 4
  • 12