Magnus Magnus - 1 year ago 76
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 Source

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

   SELECT *, COUNT(*) OVER (PARTITION BY BookProvider, BookName) AS cnt 
   FROM @BookTable TT
FROM cte
WHERE cnt > 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download