sql-server: how to select from dupilcate rows from table?

Question

Hi All,

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?

I try like 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? Can you modify that? Help me!

And I would like to show as:

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

(4 row(s) affected)

Thanks in advance!

Answer 1

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

Answer 2

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

Answer 3

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')

Answer 4

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