CODEWITHSUNDEEP
sqlpostgresql
Ross
Ross

Reputation: 82

Updating duplicate column values to append sort index from separate column

I have a person table with name and popularity columns. I am trying to figure out what SQL command I can run to update duplicate name values with an appended sort index of their relative popularity values. If possible, it would also be great if the duplicate names with top sort index 1 were left as-is and not updated. I've been noodling around with ARRAY_AGG, GROUP BY, HAVING COUNT(*) > 1,RANK () OVER, and PARTITION BY, but lack the SQL expertise to wrangle these into a working solution. I suspect I am probably going about it completely wrong. Any help or direction would be hugely appreciated!

Example

id    name    popularity
0     jane    1.5
1     joe     0.5
2     jane    0.0
3     jane    0.0
4     joe     1.4
5     emily   0.5

Desired Results

id    name    popularity
0     jane    1.5
1     joe-2   0.5
2     jane-2  0.0
3     jane-3  0.0
4     joe     1.4
5     emily   0.5

Upvotes: 0

Views: 80

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can use row_number() to enumerate the duplicate names and then join to attach this information to each row:

update person p
    set name = name || '-' || seqnum
    from (select p.*,
                 row_number() over (partition by name order by popularity desc, id) as seqnum
          from person p
         ) pp
    where pp.id = p.id and seqnum > 1;

Upvotes: 1

Abelisto
Abelisto

Reputation: 15624

select
    *,
    row_number() over (partition by name order by popularity) as num,
    name || '-' || (row_number() over (partition by name order by popularity))::text as name_new
from person;

Use UPDATE ... FROM ...

Upvotes: 1

Related Questions