Remove duplicate values from the column of a PostgreSQL table and maintain only one value per column for duplicated rows

Question

I would like to format the data of below table as follows. Here on the value column, I want to maintain only on value for each of the duplicated rows.

input table

code    value
A       10
A       10
A       10
B       20
B       20
B       20
C       30
C       30
D       40

Expected result

code    value
A        10
A   
A   
B        20
B   
B   
C        30
C   
D        40

you would like to have null instead of value? what have you tried? — Radim Bača
– Radim Bača, Commented May 7, 2019 at 8:41
Do you want to update the table, or just get these values on output? — Nick
– Nick, Commented May 7, 2019 at 8:45

Radim Bača · Accepted Answer · 2019-05-07 08:46:09Z

1

A combination of CASE and window function can solve your problem

select code, 
       case when t.rn = 1 then value else null end value
from (
    select row_number() over (partition by code, value order by value) rn,
           code, value
    from your_table
) t

answered May 7, 2019 at 8:46

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Remove duplicate values from the column of a PostgreSQL table and maintain only one value per column for duplicated rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related