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

Filed under:
|
|

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

Related posts about sql-server

Related posts about select