1

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)

Siyual
  • 16,415
  • 8
  • 44
  • 58
sen
  • 19
  • 1
  • 3
  • 6
    Please don't post pictures of code/tables. Paste it directly into your question (with proper formatting, please). Also, please share what you've attempted. – Siyual Sep 29 '16 at 18:39
  • 3
    http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 –  Sep 29 '16 at 18:45
  • @CodeDecode: that's not a duplicate. The SQL Server solution will not work with Oracle. –  Sep 29 '16 at 18:45

3 Answers3

2

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);
  • thanks for your answer. Sorry that I didn't clarify, the order table is not an actual table, it's just a screenshot from my query result. So when I run your query, it shows me an error message. So is there any way to select only one row for each order_no instead of delete others? – sen Sep 29 '16 at 19:54
  • What is the error you get? If you failed to to show us the correct table and column names then you must convert my example to the the correct names yourself. –  Sep 29 '16 at 20:00
  • I think what she/he means is, he/she didn't need to delete anything from the base table. Rather, he/she wants to know how to include only one row per order_no (it doesn't matter which one) in the result set. –  Sep 29 '16 at 20:08
  • @mathguy: the question says "remove duplicate rows". –  Sep 29 '16 at 20:13
  • I know. And I got a very high score on my TOEFL (Test Of English as a Foreign Language) and when I arrived in the U.S. still couldn't speak proper English if my life depended on it. I am interpreting from what she/he commented right below your Answer. "...to SELECT only one row for each..." and "the screenshot is not a table, it is from my query result". So she/he wants to "delete" from the query result, not from the base table or tables. –  Sep 29 '16 at 20:16
  • @sen - please see the discussion above. If my guess is correct, please edit your post, change the title to "exclude duplicate rows from query result" or something similar. I would do it myself by I am not sure my guess is correct. –  Sep 29 '16 at 20:18
1

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
;
0

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?

Vadzim
  • 24,954
  • 11
  • 143
  • 151