Reputation: 203
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
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
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
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
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