Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14),
just records where any value occurs more than once. Any suggestions?
Many thanks
Homer
Homer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer
|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer