Finding duplicate values in a SQL table

SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

Simply group on both of the columns.

Note: the older ANSI standard is to have all non-aggregated columns in the GROUP BY but this has changed with the idea of “functional dependency”:

In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.

Support is not consistent:

  • Recent PostgreSQL supports it.
  • SQL Server (as at SQL Server 2017) still requires all non-aggregated columns in the GROUP BY.
  • MySQL is unpredictable and you need sql_mode=only_full_group_by:
    • GROUP BY lname ORDER BY showing wrong results;
    • Which is the least expensive aggregate function in the absence of ANY() (see comments in accepted answer).
  • Oracle isn’t mainstream enough (warning: humour, I don’t know about Oracle).

Related Posts:

  1. Remove duplicate rows in MySQL
  2. What’s the best way to dedupe a table?
  3. What is the difference between UNION and UNION ALL?
  4. Selecting COUNT(*) with DISTINCT
  5. ORA-00904: invalid identifier
  6. Unknown column in ‘field list’ error on MySQL Update query
  7. The wait operation timed out. ASP
  8. When should I use CROSS APPLY over INNER JOIN?
  9. Insert Data Into Temp Table with Query
  10. How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  11. SQL SELECT WHERE field contains words
  12. Using group by on multiple columns
  13. Efficiently convert rows to columns in sql server
  14. How to delete duplicate rows in SQL Server?
  15. Nested select statement in SQL Server
  16. What’s the difference between VARCHAR and CHAR?
  17. ORA-01843 not a valid month- Comparing Dates
  18. How do I escape a single quote in SQL Server?
  19. How do you trouble shoot a “Data type mismatch in criteria expression” error in MS Access 2010?
  20. Inserting data into a temporary table
  21. How to Select Top 100 rows in Oracle?
  22. CREATE VIEW must be the only statement in the batch
  23. SQL Server FOR EACH Loop
  24. How can I select the first day of a month in SQL?
  25. Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio
  26. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  27. Oracle Apex column link from LOV values
  28. When to use “ON UPDATE CASCADE”
  29. MySQL combine two columns into one column
  30. ORA-01861: literal does not match format string
  31. SQL Server Insert if not exists
  32. SQL Server: Invalid Column Name
  33. How do I do multiple CASE WHEN conditions using SQL Server 2008?
  34. Insert text with single quotes in PostgreSQL
  35. String or binary data would be truncated. The statement has been terminated
  36. How to insert date values into table
  37. Error converting data type varchar to float
  38. How to concatenate text from multiple rows into a single text string in SQL Server
  39. Count(*) vs Count(1) – SQL Server
  40. Arithmetic overflow error converting numeric to data type numeric
  41. I want to use CASE statement to update some records in sql server 2005
  42. “You tried to execute a query that does not include the specified aggregate function”
  43. How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?
  44. How to round down to nearest integer in MySQL?
  45. How do I reset a sequence in Oracle?
  46. Query to convert from datetime to date mysql
  47. MySQL error: key specification without a key length
  48. SQL: Two select statements in one query
  49. How to query MongoDB with “like”
  50. Check if MySQL table exists without using “select from” syntax?
  51. MySQL equivalent of DECODE function in Oracle
  52. mysql update column with value from another table
  53. what is the difference between triggers, assertions and checks (in database)
  54. Replacing NULL with 0 in a SQL server query
  55. SQL Server dynamic PIVOT query?
  56. How to delete from multiple tables in MySQL?
  57. How to copy a row and insert in same table with a autoincrement field in MySQL?
  58. Error converting data type varchar
  59. I want to use CASE statement to update some records in sql server 2005
  60. What does Include() do in LINQ?
  61. SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
  62. Get everything after and before certain character in SQL Server
  63. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  64. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  65. ROW_NUMBER() in MySQ
  66. LEFT function in Oracle
  67. What are database constraints?
  68. Postgresql column reference “id” is ambiguous
  69. Inner Joining three tables
  70. How do I view the Explain Plan in Oracle Sql developer?
  71. ORA-01779: cannot modify a column which maps to a non key-preserved table
  72. SUM OVER PARTITION BY
  73. SQLite string contains other string query
  74. Varchar invalid for Sum operator
  75. SQL: How to properly check if a record exists
  76. Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF
  77. Sorting search results by taxonomy terms
  78. SQL select of users by metadata
  79. How to properly sanitize strings without $wpdb->prepare?
  80. WordPress SQL LIKE request doesn’t work for fields with special symbols
  81. posts_where Fails with More than One Custom Field in Query
  82. WordPress database error: You have an error in your SQL syntax
  83. Restose content from revisions – sql query
  84. Better way to migrate to server?
  85. Inner Join user tables to select users with roles
  86. $wpdb->prepare with LIKE returning blank array instead of rows
  87. Removing posts by sql
  88. Why line returns are not reapply after doing esc_sql?
  89. WooCommerce database query : Get product category image path+name from thumbnail_id [closed]
  90. WordPress can I manually add columns in users table and display it in the default manage user plugin and registration form? [closed]
  91. Help me SELECT thumbnail from SQL and use
  92. Why is my insert row only inserting the final row from the loop into the database rather than just inserting one
  93. Search results sort order failing: set by date only
  94. SQL query: select duplicate posts – but using duplicate meta [duplicate]
  95. Creating a “forum” – showing last post or last commented post
  96. Alter SQL query to return posts with unique custom field value, no duplicate values
  97. Replace Unwanted Space in Post Content URL
  98. SQL to transform all email addresses in my DB in lowercase [closed]
  99. How to write inner join using posts_clauses?
  100. This SQL request call all time and overload my server : SELECT meta_value FROM wp_sitemeta WHERE meta_key = ‘wp_installer_network’ AND site_id = 1

Leave a Comment

tech