Magnus Magnus - 1 month ago 5
SQL Question

Find duplicates in in sql server

I have a talble like this..

BookId BookName BookProvider

1 Angels and deamons Amazon
2 Angels and deamons Amazon
3 Angels and deamons Ebay
4 Tuesdays Amazon
5 SQL 101 Ebay


I would like to find all Book Names that are identical and exists in more than one unique BookProvider, how can I do that in a SQL query? In this example I would like to retrieve record 1 and 2 since tha book name is identical and it can be found on both amazon and ebay.

Answer

This returns all books with multiple rows for the same BookProvider:

WITH cte AS
 (
   SELECT *, COUNT(*) OVER (PARTITION BY BookProvider, BookName) AS cnt 
   FROM @BookTable TT
 )
SELECT * 
FROM cte
WHERE cnt > 1;