sql-server: how to select from dupilcate rows from table?
Posted
by RedsDevils
on Stack Overflow
See other posts from Stack Overflow
or by RedsDevils
Published on 2010-04-09T05:14:30Z
Indexed on
2010/04/09
5:23 UTC
Read the original article
Hit count: 385
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! Thanks in advance!
© Stack Overflow or respective owner