CODEWITHSUNDEEP
sqloracle-database
uweselbst
uweselbst

Reputation: 203

Oracle: Delete duplicates in a group from Table with join

Desire Output is:

I want to delete duplicates from a table by a group.

My tables :

rc_document:

+----------------+-------------+----------------------+
| rc_document_id | document_id | rc_document_group_id |
+----------------+-------------+----------------------+
|              1 |           1 |                    1 |
|              2 |           2 |                    1 |
|              3 |           3 |                    1 |
|              4 |           4 |                    1 |
|              5 |           1 |                    2 |
|              6 |           3 |                    2 |
+----------------+-------------+----------------------+

rc_document_group:

+----------------------+----------+
| rc_document_group_id | priority |
+----------------------+----------+
|                    1 |        1 |
|                    2 |        2 |
+----------------------+----------+

I only want to keep the rc_documents whose rc_document_group has the highest priority. All other entries with the same "document_id" should be deleted. In other words ... document_id should only be in the rc_document_group with the highest priority, the other ones should be deleted

here is my expected result: 

+----------------+-------------+----------------------+
| rc_document_id | document_id | rc_document_group_id |
+----------------+-------------+----------------------+
|              2 |           2 |                    1 |
|              4 |           4 |                    1 |
|              5 |           1 |                    2 |
|              6 |           3 |                    2 |
+----------------+-------------+----------------------+

Upvotes: 0

Views: 91

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Use Oracle's KEEP LAST to find the best rc_document_id per document_id. Then delete all others.

delete from rc_document
where rc_document_id not in
(
  select max(d.rc_document_id) keep (dense_rank last order by dg.priority)
  from rc_document d
  join rc_document_group dg using (rc_document_group_id)
  group by d.document_id
);

Rextester demo: http://rextester.com/NZVZGF52818

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10711

Use row_number window function

select *
from (
  select *, row_number() over (partition by document_id order by priority desc) rn
  from rc_document d
  join rc_document_group dg on d.rc_document_group_id  = dg.rc_document_group_id 
) t
where t.rn = 1

Upvotes: 0

Pankaj Kumar
Pankaj Kumar

Reputation: 570

For unique selection

     SELECT MAX(rd.rc_document_id),rd.document_id,
     rd.rc_document_group_id
     FROM rc_document rd 
     INNER JOIN rc_document_group rdg ON   
     rd.rc_document_group_id = rdg.rc_document_group_id
     GROUP BY rd.document_id,rd.rc_document_group_id ;

For deletion run this query

     DELETE FROM rc_document WHERE rc_document_id NOT IN (
     SELECT MAX(rd.rc_document_id)
     FROM rc_document rd 
     INNER JOIN rc_document_group rdg ON   
     rd.rc_document_group_id = rdg.rc_document_group_id
     GROUP BY rd.document_id,rd.rc_document_group_id);

Upvotes: 0

Pelin
Pelin

Reputation: 966

You can use not exists to check if the record has the highest priority.

SELECT
    *
FROM
    rc_document rc,
    rc_document_group rcg
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            rc_document rcsub,
            rc_document_group rcgsub
        WHERE
            rc.documetn_id == rcsub.document_id
        AND rc.rc_document_id != rcsub.rc_document_id
        AND rc.priority < rcsub.priority
    )

Upvotes: 0

Related Questions