1. We have moved to a new forum! There may be a few things not working properly so please let us know if you find a bug. Remember to use the bbCode [ sql ] tag for SQL statements.
New XenForo forum! Google Ads

Find duplicate entries in a table?

Discussion in 'SQL - Questions and Answers' started by ben, Apr 15, 2006.

  1. ben Administrator

    hihelloANY
    ben, Apr 15, 2006
    #1

  2. ben Administrator

    Dialect: SQL 92
    Let's assume we want to find duplicate emails in a 'users' table:

    SQL:

    
SELECT id, email, count(email) FROM users GROUP BY email HAVING count(email) > 1
    ben, Apr 17, 2006
    #2

  3. ben Administrator

    Dialect: MySQL
    Another approach is to list all the duplicate entries. We assume the field 'id' is the primary key of the table 'users'

    SQL:

    
SELECT DISTINCT t1.id, t1.email FROM users t1, users t2 WHERE t1.email = t2.email AND t1.id <> t2.id
    ben, Apr 17, 2006
    #3

  4. Rob137 Guest

    Dialect: MS SQL
    SQL:

    
select *
    
from Something as zd 
    
where (select count(*) from Something as t1 
    
     where = zd.liefschnum and t1.pos = zd.pos) > 1
    Rob137, May 8, 2006
    #4

  5. Rock Guest

    Dialect: MS SQL
    SQL:

    
SELECT empno,count(*) as counter FROM emp
    Rock, Mar 18, 2007
    #5

  6. Unregistered Guest

    SQL:
    select t1.field_name from tablename t1, tablename t2 where t1.field_name=t2.field_name and t1.rowid <> t2.rowid;
    Unregistered, Apr 1, 2008
    #6

  7. Unregistered Guest

    SQL:
    select e.column_name1 
    
from table_name e, table_name f 
    
where e.column_name1 =f.column_name1 and e.column_name2=f.column_name2
    Unregistered, Jun 16, 2008
    #7

  8. dandv New Member

    That doesn't work in PostgreSQL:
    ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function
    dandv, Jul 16, 2008
    #8

  9. Dimitar Guest

    Yes, the statement is not valid according to SQL standards. It a documented behaviour of MySQL.
    Dimitar, Jul 16, 2008
    #9

Share This Page