Find duplicate entries in a table?

ben · #2 (**permalink**) 04-17-2006, 01:38 AM

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


SELECT id, email, count(email) FROM users GROUP BY email HAVING count(email) > 1

ben · #3 (**permalink**) 04-17-2006, 01:41 AM

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


SELECT DISTINCT t1.id, t1.email FROM users t1, users t2 WHERE t1.email = t2.email AND t1.id <> t2.id

#4 (**permalink**) 05-07-2006, 11:06 PM


SELECT *

FROM Something AS zd 

WHERE (SELECT count(*) FROM Something AS t1 

     WHERE = zd.liefschnum AND t1.pos = zd.pos) > 1

#5 (**permalink**) 03-18-2007, 01:26 AM


SELECT empno,count(*) AS counter FROM emp

#6 (**permalink**) 04-01-2008, 07:30 AM

SELECT t1.field_name FROM tablename t1, tablename t2 WHERE t1.field_name=t2.field_name AND t1.rowid <> t2.rowid;

#7 (**permalink**) 06-16-2008, 12:11 PM

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

dandv · #8 (**permalink**) 07-16-2008, 08:45 AM

Quote:

Originally Posted by ben

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


SELECT id, email, count(email) FROM users GROUP BY email HAVING count(email) > 1

That doesn't work in PostgreSQL:
ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function

#9 (**permalink**) 07-16-2008, 05:43 PM

Quote:

Originally Posted by dandv

That doesn't work in PostgreSQL:
ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function

Yes, the statement is not valid according to SQL standards. It a documented behaviour of MySQL.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

LinkBacks (?) LinkBack to this Thread: http://www.sqlrecipes.com/sql_questions_answers/find_duplicate_entries_table-5/
Posted By	For	Type	Date
PHP script resource - [MSSQL] leidige theme Dubletten	This thread	Refback	03-06-2008 01:29 PM
Discover From Your Favorite Topic or Web Page: www.w3schools.com/sql/sql_join.asp	This thread	Refback	02-29-2008 05:31 AM
PHP script resource [MSSQL] leidige theme Dubletten	This thread	Refback	08-19-2007 09:51 PM
PHP script resource - [MSSQL] leidige theme Dubletten	This thread	Refback	07-11-2007 10:45 AM
PHP script resource - [MSSQL] leidige theme Dubletten	This thread	Refback	05-31-2007 02:25 PM

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Selecting Duplicate Entries in a table?	Simon	SQL - Questions and Answers	6	06-09-2008 11:15 PM

User Name		Remember Me?
Password

ANSWER(S):