Finding and Deleting duplicate rows in table where multiple occurances of records is allowed

Question

I have a table that I need to delete duplicates. However, the table is designed so that each customer can have identical items. Here is a screen shot for example:

When I try to delete duplicates I also get Book Number that are the same for both customer. The Green area shows actual duplicates, however the blue shows books that are not duplicate because customers can borrow the same books.

How do I delete only where the row is the same for each customer? So the green area.

Here is my code: It does not work if both customer has the same Book number.

WITH CTE AS
(
SELECT  *, 
ROW_NUMBER() OVER (PARTITION BY BookNumber ORDER BY BookNumber DESC) AS DUPS
FROM Store.Books 
)

SELECT * FROM CTE WHERE DUPS > 1

Answer 1

the below query should give you a list of all the duplicate booknumbers with the customerIDs, all you need to do is a simple delete statement with the results to remove the duplicate records

SELECT count(bookNumber), booknumber, customerID FROM TableName GROUP BY booknumber, customerID having count(booknumber)> 1

Answer 2

WITH CTE AS
(
SELECT  *, 
ROW_NUMBER() OVER (PARTITION BY BookNumber, Customerid ORDER BY BookNumber) AS DUPS
FROM Store.Books 
)
SELECT * FROM CTE WHERE DUPS > 1
-- if you want to delete, replace last line with this:
--DELETE FROM CTE WHERE DUPS > 1

I should mention that order by booknumber desc wasn't necessary so I removed the 'desc' part

Answer 3

Brothers, This is one of the way to find out duplicates data, try it=)

DECLARE @tempTable TABLE(
CustomerID SMALLINT,
BookLoan NVARCHAR(255),
BookNumber INT,
BookAuthor NVARCHAR(255)
)

INSERT INTO @tempTable Values(112,'Clash Of Titans',12345,'Dick VanDyke ')
INSERT INTO @tempTable Values(112,'Clash Of Titans',12345,'Dick VanDyke ')
INSERT INTO @tempTable Values(112,'Clash Of Titans',23457,'Dick VanDyke ')
INSERT INTO @tempTable Values(112,'History of Soda',99899,'Brian Adams  ')

Select *,Count(*) 'Occurrance' From @tempTable Group by 
CustomerID,BookLoan,BookNumber,BookAuthor having count(*) > 1


Delete from @temptable
where CustomerID = (Select customerID From @tempTable Group by
CustomerID,BookLoan,BookNumber,BookAuthor having count(*) > 1)
AND BookLoan = (Select BookLoan From @tempTable Group by 
CustomerID,BookLoan,BookNumber,BookAuthor having count(*) > 1)
AND BookNumber = (Select BookNumber From @tempTable Group by 
CustomerID,BookLoan,BookNumber,BookAuthor having count(*) > 1)
AND BookAuthor = (Select BookAuthor From @tempTable Group by 
CustomerID,BookLoan,BookNumber,BookAuthor having count(*) > 1)

Select * from @tempTable

Or alternative way as below here

DECLARE @tempTable TABLE(
CustomerID SMALLINT,
BookLoan NVARCHAR(255),
BookNumber INT,
BookAuthor NVARCHAR(255)
)

INSERT INTO @tempTable Values(112,'Clash Of Titans',12345,'Dick VanDyke ')
INSERT INTO @tempTable Values(112,'Clash Of Titans',12345,'Dick VanDyke ')
INSERT INTO @tempTable Values(112,'Clash Of Titans',23457,'Dick VanDyke ')
INSERT INTO @tempTable Values(112,'History of Soda',12345,'Brian Adams  ')

;WITH CTE AS
(
SELECT  *, 
ROW_NUMBER() OVER (PARTITION BY CustomerID, BookLoan,BookNumber,BookAuthor 
ORDER BY BookNumber) AS DUPS
FROM @tempTable
)
DELETE FROM @tempTable
WHERE CustomerID = (SELECT CustomerID FROM CTE WHERE DUPS > 1)
AND BookLoan = (SELECT BookLoan FROM CTE WHERE DUPS > 1)
AND BookNumber = (SELECT BookNumber FROM CTE WHERE DUPS > 1)
AND BookAuthor = (SELECT BookAuthor FROM CTE WHERE DUPS > 1)

SELECT * FROM @tempTable