Delete multiple duplicates leaving max id in sql

Question

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

Answer 1

Keep it simple.

DELETE FROM tblInvoices 
WHERE ID NOT IN
   (SELECT MAX(ID)
   FROM tblInvoices
   GROUP BY EncID, PermitID)

Answer 2

I was able to get it working with the current SQL Statement

WITH CTE AS
(
    SELECT ROW_NUMBER() OVER (Partition BY PermitID ORDER BY ID) AS RowNumber, *
    FROM tblInvoices
    WHERE EncID = '0237'
)

DELETE FROM CTE 
WHERE RowNumber < 13

Answer 3

You can try this:

WITH cte AS
(  
SELECT row_number() OVER (PARTITION by permitid ORDER BY Id DESC) r,ID,permitid,encid 
FROM tblinvoices    
)
DELETE FROM cte WHERE r > 1