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


    Regards,
    Bob Szymanski
    Blue Mule Software

  • 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]

    &nbsp 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!


    Regards,
    Bob Szymanski
    Blue Mule Software

  • OK, I got it. Thanks Kim.


    Regards,
    Bob Szymanski
    Blue Mule Software

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply