Consider a table like this
CREATE TABLE testtable (
[ID] [int] NOT NULL
[col1] [int] NOT NULL
)
INSERT INTO testtable VALUES
(1,25),
(2,25),
(3,13),
(4,43),
(5,7),
(6,43)
I want only the rows that have col1 = x more than once. Like such
CREATE TABLE resulttable (
[ID] [int] NOT NULL
[col1] [int] NOT NULL
)
INSERT INTO resulttable VALUES
(1,25),
(2,25),
(4,43),
(6,43)
I do it like this
SELECT ID, col1 FROM testtable a
where (SELECT COUNT(col1) FROM testtable b WHERE a.col1 = b.col1 GROUP BY col1) > 1
ORDER BY a.col1
I would like to know other ways, maybe something more easy to remember.