Menu
I have a database that contains numerous amounts of duplicates, each have a unique ID but their PermitID and EncID are the same. I need to remove all but the highest ID in the database.
The sql statement,
DELETE FROM tblInvoices
WHERE EncID = '0237' AND PermitID IN (
SELECT Max(ID) FROM tblInvoices Group BY PermitID)
deletes all of the records. I have tried
DELETE FROM tblInvoices
WHERE EncID = '0237' AND PermitID
< (SELECT Max(ID) FROM tblInvoices Group BY PermitID)
but I receive the error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
An example of the data would be
ID PermitID EncID
1 11 22
2 11 22
3 11 22
4 12 23
5 12 23
I would want to keep 3, remove 2 and 1. I would also like to keep 5 and remove 4
770
RANK function to SQL delete duplicate rows We can use the SQL RANK function to remove the duplicate rows as well. SQL RANK function gives unique row ID for each row irrespective of the duplicate row. In the following query, we use a RANK function with the PARTITION BY clause.
(1) Select Fruit column (which you will remove duplicates rows by), and then click the Primary Key button; (2) Select the Amount column (Which you will keep highest values in), and then click Calculate > Max. (3) Specify combination rules for other columns as you need.
1. If you want to remove all duplicates but leave the highest ones, you can apply this formula =MAX(IF($A$2:$A$12=D2,$B$2:$B$12)), remember to press Shift + Ctrl + Enter keys. 2. In the above formulas, A2:A12 is the original list you need to remove duplicates from.
The go to solution for removing duplicate rows from your result sets is to include the distinct keyword in your select statement. It tells the query engine to remove duplicates to produce a result set in which every row is unique.
Keep it simple.
DELETE FROM tblInvoices
WHERE ID NOT IN
(SELECT MAX(ID)
FROM tblInvoices
GROUP BY EncID, PermitID)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
