locked
Struggling with duplicates across multiple fields RRS feed

  • Question

  • I can find and display duplicates easily in a single column but struggle when it comes to multiple columns and was wondering if anyone can point me in the right direction.

    I have a few fields I need to display firstname, surname, mobile, phone-ah and phone-bh and the duplicates could occur in the mobile, phone-ah and mobile-bh fields across multiple records.

    Is this possible to do or would i do better doing each row manually.

    Thanks


    Friday, January 15, 2010 12:32 AM

Answers

  • SQL Server 2005 and up

    select * from  (select *, count(*) over(partition by FirstName, SurName, Mobile, [Phone-ah], [Phone-bh]) as CountDups from myTable) X where CountDups >1
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Paul Devine Friday, January 15, 2010 2:30 AM
    Friday, January 15, 2010 12:50 AM

All replies

  • Would you be able to post your table schema (including constraints) and the query that you have been working from?

    -E
    Friday, January 15, 2010 12:37 AM
  • Very simple table

    CREATE TABLE [dbo].[clients](
    	[clientID] [int] IDENTITY(1,1) NOT NULL,
    	[attention] [nvarchar](255) NULL,
    	[email] [nvarchar](255) NULL,
    	[fax] [nvarchar](255) NULL,
    	[firstname] [nvarchar](255) NULL,
    	[mobile] [nvarchar](255) NULL,
    	[phone-ah] [nvarchar](255) NULL,
    	[phone-bh] [nvarchar](255) NULL,
    	[salutation] [nvarchar](255) NULL,
    	[surname] [nvarchar](255) NULL,
    	[propertyid-fk] [int] NULL,
    	[postal-propertyid-fk] [int] NULL,
    	[clientid-pk] [int] NULL
    ) ON [PRIMARY]
    The Query I am working on is the following but I am unsure of validity of the results.

    SELECT  *
    FROM    [BoomCRM].[dbo].[clients] cl
    WHERE   EXISTS
            (
            SELECT  1
            FROM    [BoomCRM].[dbo].[clients] cd
            WHERE   cd.[mobile] = cl.[phone-ah]
                    OR cd.[mobile] = cl.[phone-bh] 
                    AND cd.[clientid-pk]  <> cl.[clientid-pk]                                
            )

    Friday, January 15, 2010 12:50 AM
  • SQL Server 2005 and up

    select * from  (select *, count(*) over(partition by FirstName, SurName, Mobile, [Phone-ah], [Phone-bh]) as CountDups from myTable) X where CountDups >1
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Paul Devine Friday, January 15, 2010 2:30 AM
    Friday, January 15, 2010 12:50 AM
  • Naomi that works fantastic, thank you.
    Friday, January 15, 2010 2:30 AM
  • You're welcome.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, January 15, 2010 2:46 AM