How to remove duplicate value from a row

Question

I have a dataset like:

order_id | order_item_id | category
1        | 1             | book
1        | 2             | pen
1        | 3             | book

now I have to remove the order_item_id and its row that has duplicate value on category columns but still leave 1 of them. How can i achieve that?

Duplicate of [Delete Duplicate Records in PostgreSQL](https://stackoverflow.com/questions/6583916/delete-duplicate-records-in-postgresql) — Kaushik Nayak, Jan 04 '19 at 06:56
@NicoHaase duplicate value means there are some category values that same in 1 order id — Brenda Natasha, Jan 04 '19 at 07:08

score 0 · Answer 1 · answered Jan 04 '19 at 10:52

0

delete from mytable where order_item_id not in (select max(order_item_id) from mytable group by order_ID,category)

answered Jan 04 '19 at 10:52

Deependra Bhandari

51
2

score 0 · Accepted Answer · answered Jan 04 '19 at 11:01

0

Delete if there is a row with the same order_id and category but with less order_item_id:

delete from orders o
where exists (
  select 1 
  from orders 
  where 
    orders.order_id = o.order_id
    and
    orders.category = o.category
    and 
    orders.order_item_id < o.order_item_id
  );

See the demo

answered Jan 04 '19 at 11:01

forpas

160,666
10
38
76

Sorry im new, how to accept the answer? I can't upvote it :( – Brenda Natasha Jan 04 '19 at 15:05

score 0 · Answer 3 · answered Jan 04 '19 at 12:15

I would simply do:

delete from t
    where t.order_item_id > (select min(t2.order_item_id)
                             from t t2
                             where t2.order_id = t.order_id and
                                   t2.category = t.category
                            );

How to remove duplicate value from a row

3 Answers3