Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    92

    Unanswered: duplicate record

    Dear All,

    I need to identify duplicate records in a table. TableA [ id, firstname, surname] Id like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table

    Im not sure how to write this query, can someone help? Thanks in advance!

  2. #2
    Join Date
    Apr 2004
    Posts
    11
    try something like this:



    select id, fname, lastname, count(*)
    from tablename
    having count(*) > 1

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use a subquery with the HAVING clause to isolate duplicated firstname/lastname records, then link to the table to get id values:
    Code:
    select	YourTable.id,
    	YourTable.firstname,
    	YourTable.surname,
    	YourSubquery.occurances
    from	YourTable
    	inner join --YourSubquery
    		(select	firstname,
    			surname,
    			count(*) as Occurances
    		from	YourTable
    			group by firstname,
    			surname
    		having count(*) > 1) YourSubquery
    		on YourTable.firstname = YourSubquery.firstname
    		and YourTable.surname = YourSubquery.surname
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Mar 2005
    Posts
    92
    Quote Originally Posted by chosen_silver
    try something like this:



    select id, fname, lastname, count(*)
    from tablename
    having count(*) > 1

    Hmm ok, doesn't look complex at all, thanks!

    It gave me error messages about not having a grouped by statement in there, so I added it. It works fine, thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •