CODEWITHSUNDEEP
sqloracle-databaseduplicates
sen
sen

Reputation: 19

Remove duplicate rows in a table

I have a table contains order information like below:

Order table: enter image description here

As we can see from that table, each order_no has several duplicates. So what I want is to keep only one row for each order_no (no matter which one it is)

Is anyone knows how to do this? (FYI, I am using Oracle 10)

Upvotes: 1

Views: 635

Answers (3)

Vadzim
Vadzim

Reputation: 26200

Here is a query to remove semi-duplicates with precise order by differing field:

delete from message_part
where ts >= since and ts < since + 1
    and rowid not in (
        select distinct first_value(rowid) over (partition by id order by differing_field)
        from message_part where ts >= since and ts < since + 1
    );

Credits go to @a_horse_with_no_name and Oracle equivalent of Postgres' DISTINCT ON?

Upvotes: 0

user5683823
user5683823

Reputation:

If you don't care which row you get for each order_no, perhaps the simplest solution (before Oracle 12) is:

select [whatever columns you want, probably not rn - see below]
from ( select order_table.*,
              row_number() over (partition by order_no order by null) as rn
     )
where rn = 1
;

Upvotes: 1

user330315
user330315

Reputation:

This should work, even in your ancient and outdated Oracle version:

delete from order_table
where rowid not in  (select min(rowid)
                     from order_table
                     group by order_no);

Upvotes: 2

Related Questions