CODEWITHSUNDEEP
sqlsql-serverduplicates
MTplus
MTplus

Reputation: 2391

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.

Upvotes: 0

Views: 66

Answers (6)

dnoeth
dnoeth

Reputation: 60462

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;

Upvotes: 1

jarlh
jarlh

Reputation: 44696

Do you want the whole rows, including Bookid and BookProviders? Have a sub-query that returns the booknames that can be found in more than one store. Join with that result.

select t1.*
from tablename t1
join (select BookName from tablename
      group by BookName
      having count(distinct BookProvider) > 1) t2
    on t1.BookName = t2.BookName

Upvotes: 1

Draco
Draco

Reputation: 16364

You can even use a CTE with a partion as well if you want to view all the books and their counts:

DECLARE @BookTable TABLE
(
    BookId int,
    BookName varchar(100),
    BookProvider varchar(100)
)

INSERT INTO @BookTable
(
    BookId,
    BookName,
    BookProvider
)
VALUES
(1, 'Angel and Demons', 'Amazon'),
(2, 'Angel and Demons', 'EBay'),
(3, 'Tuesdays','Amazon'),
(4, 'SQL 101', 'Amazon');

WITH BookCte 
AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY TT.BookName ORDER BY TT.BookName) as RowNumber FROM @BookTable TT
)

SELECT * FROM BookCte BC

Upvotes: 0

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

this:

select count(bookname),bookname from tablename group by bookname having count(bookname)>1

Upvotes: 0

connectedsoftware
connectedsoftware

Reputation: 7087

DECLARE @BookProvider TABLE(
    Id int
    ,BookName VARCHAR(100)
    ,BookProvider VARCHAR(100)
)

INSERT INTO @BookProvider
VALUES(1, 'Angels and Demons', 'Amazon')
INSERT INTO @BookProvider
VALUES(2, 'Angels and Demons', 'Ebay')
INSERT INTO @BookProvider
VALUES(3, 'Tuesdays', 'Amazon')
INSERT INTO @BookProvider
VALUES(4, 'SQL 101', 'Ebay')

SELECT BookName, COUNT(DISTINCT BookProvider) as Count FROM @BookProvider 
GROUP BY BookName
HAVING COUNT(DISTINCT BookProvider) > 1

Results:

+-------------------+-------+
|     BookName      | Count |
+-------------------+-------+
| Angels and Demons |     2 |
+-------------------+-------+

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Try this

select  BookName from table
group by BookName
having count(distinct BookProvider)>1

Upvotes: 5

Related Questions