CODEWITHSUNDEEP
sql-serverselectduplicate-data
RedsDevils
RedsDevils

Reputation: 1433

How to select from duplicate rows from a table?

I have the following table:

CREATE TABLE TEST(ID TINYINT NULL, COL1 CHAR(1))
INSERT INTO TEST(ID,COL1) VALUES (1,'A')
INSERT INTO TEST(ID,COL1) VALUES (2,'B')
INSERT INTO TEST(ID,COL1) VALUES (1,'A')
INSERT INTO TEST(ID,COL1) VALUES (1,'B')

INSERT INTO TEST(ID,COL1) VALUES (1,'B')
INSERT INTO TEST(ID,COL1) VALUES (2,'B')

I would like to select duplicate rows from that table. How can I select them?

I tried the following:

SELECT TEST.ID,TEST.COL1
FROM TEST WHERE TEST.ID IN
(SELECT ID
FROM TEST WHERE TEST.COL1 IN
(SELECT COL1
FROM TEST WHERE TEST.ID IN
(SELECT ID
FROM TEST
GROUP BY ID
HAVING COUNT(*) > 1)
GROUP BY COL1
HAVING COUNT(*) > 1)
GROUP BY ID
HAVING COUNT(*) > 1)

Where's the error? What do I need to modify?

And I would like it to show as:

ID   COL1
---- ----
1    A
1    A
1    B
1    B

(4 row(s) affected)

Upvotes: 0

Views: 222

Answers (4)

Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

SELECT t.* 
FROM TEST t
           INNER JOIN (
                       SELECT ID,COL1 
                       from test 
                       GROUP BY ID,COL1
                       HAVING COUNT(*) > 1
                      ) 
                       AS t2 
                       ON t2.ID = t.ID AND t2.COL1 =t.COL1
order by t.ID,t.COL1

Upvotes: 0

Wikser
Wikser

Reputation: 820

SELECT id, col1
FROM Test
GROUP BY id, col1
HAVING COUNT(*) > 1

when you use

SELECT id, col1, COUNT(*) AS cnt
FROM Test
GROUP BY id, col1
HAVING COUNT(*) > 1

you practically have all duplicate rows and how often they appear. You can't identify them individually either way.

A slower way would be:

SELECT id, col1
FROM Test T
WHERE (SELECT COUNT(*)
       FROM Test I
       WHERE I.id = T.id AND I.col1 = T.col1) > 1

Upvotes: 4

Adriaan Stander
Adriaan Stander

Reputation: 166326

Using Sql Server 2005+ and CTE you could try

;WITH Dups AS (
    SELECT *,
            ROW_NUMBER() OVER(PARTITION BY ID, Col1 ORDER BY ID, Col1) Rnum
    FROM @TEST t
)
SELECT *
FROM Dups
WHERE Rnum > 1

OR just a standard

SELECT  ID, 
        Col1,
        COUNT(1) Cnt
FROM    @TEST
GROUP BY    ID, 
            Col1
HAVING  COUNT(1) > 1

EDIT:

Display duplicate rows

SELECT  t.*
FROM    @Test t INNER JOIN
        (
            SELECT  ID,  
                    Col1, 
                    COUNT(1) Cnt 
            FROM    @TEST 
            GROUP BY    ID,  
                        Col1 
            HAVING  COUNT(1) > 1 
        ) dups  ON  t.ID = dups.ID 
                AND t.Col1 = dups.Col1

Upvotes: 1

anger
anger

Reputation: 1018

Every row in that set of data is a duplicate

select id, col1, count(*)
from test
group by id, col1

shows this

if you want to exclude the 2,B rows you need to do it explicitly

eg

SELECT id, col1
FROM Test
WHERE NOT (id = 2 and col1 = 'B')

Upvotes: 0

Related Questions