Need help with duplicates column values (newbie)
-
Greetings, I need some help building a query and was hoping someone could help.
I have a table that looks like the following:
A B
3 13
5 17
5 18
6 13
I need to know what item(s) in column A have duplicate Column B entries.
So for this example the result set would be 3 and 6 as the number 13 is found in each.
Thanks in advance
-
Hi Bob,
Hope this works for you.
Kim
--create test table in tempdb
Use tempdb
CREATE TABLE [TestData] (
[PKID] [int] IDENTITY (400, 1) NOT NULL ,
[OtherID] [int] NULL CONSTRAINT [PK_APPEALS] PRIMARY KEY CLUSTERED
(
[PKID]
 
WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]
GO
--insert test data
insert into tempdb.dbo.TestData (otherid) values (13)
insert into tempdb.dbo.TestData (otherid) values (17)
insert into tempdb.dbo.TestData (otherid) values (18)
insert into tempdb.dbo.TestData (otherid) values (13)
insert into tempdb.dbo.TestData (otherid) values (15)
--select the results
select td.pkid,td.otherid,dups totalcount
from (
select otherid,count(*) dups from tempdb.dbo.TestData
group by otherid
) derivedtable
inner join tempdb.dbo.testdata td on td.otherid = derivedtable.otherid
where dups >= 2
-
Hi Kim, thanks for your help.
I was able to use your select to query my table it returned the following results:
pkid otherid totalcount
6132
3132
5432
3432
3442
5442
Which is correct! Nice work!! I appreciate it.
How can I tweak it slightly to only get the pkids in the results (no duplicates)
Thanks again. There is no way I could have figured that out myself. You rock!
-
OK, I got it. Thanks Kim.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply