www.webdeveloper.com
Results 1 to 8 of 8

Thread: Find duplicates in table

  1. #1
    Join Date
    Nov 2010
    Posts
    56

    Find duplicates in table

    Hey, I am trying to find rows that have the same value for 2 columns in my table. Specifically duplicate people in the table (same first and last names).
    I have tried the following (plus some variations):
    Code:
    SELECT * FROM `table_name` as t1
    INNER JOIN `table_name` as t2
    ON t1.last_name = t2.last_name AND t1.first_name = t2.first_name
    which returns heaps of rows (not sure why). I've also tried:
    Code:
    select   first_name,
             last_name,
             count(*)
    from    table_name
    group by first_name,
             last_name
    having   count(*) > 1
    But I think this is finding rows that have the same first name OR last name, not both in the same row..

    How do I do this?
    Thanks in advance!

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,334
    Is there a primary or unique key on that table?
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,334
    This seems to work in MySQL:
    Code:
    SELECT
      COUNT(*) AS num_instances,
      CONCAT_WS(', ', last_name, first_name) AS full_name
    FROM table_name
    ORDER BY num_instances DESC
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    Nov 2010
    Posts
    56
    Quote Originally Posted by NogDog View Post
    Is there a primary or unique key on that table?
    Yeah, there is an int as a unique id

    Quote Originally Posted by NogDog View Post
    This seems to work in MySQL:
    Code:
    SELECT
      COUNT(*) AS num_instances,
      CONCAT_WS(', ', last_name, first_name) AS full_name
    FROM table_name
    ORDER BY num_instances DESC
    Hmm, that seems to be returning all rows with num_instances as the full row count..

  5. #5
    Join Date
    Nov 2010
    Posts
    56
    I got it working by changing that up a little:
    Code:
    SELECT 
        CONCAT_WS(', ', first_name, last_name) AS full_name, 
        COUNT(*) AS num_res 
    FROM table_name GROUP BY full_name 
    ORDER BY num_res DESC
    Thanks NogDog!

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,334
    Oops, looks like I left out a line:
    Code:
    SELECT
      COUNT(*) AS num_instances,
      CONCAT_WS(', ', last_name, first_name) AS full_name
    FROM table_name
    GROUP BY full_name
    ORDER BY num_instances DESC
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,334
    heh...must have been typing while you posted
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  8. #8
    Join Date
    Nov 2010
    Posts
    56
    haha, all good! thanks dude!
    One more thing I'm wondering, is it possible to run this query but have it show the duplicated rows? What I need to do is compare some of the other columns then decide which on of the duplicated I want to delete..
    I was thinking that the group by last_name might stop me from doing this (unless I somehow group and then ungroup to display)?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles