Find rows that have the same value on a column in MySQL

by

This query will give you a list of email addresses and how many times they’re used, with the most used addresses first.

SELECT email,
       count(*) AS c
FROM TABLE
GROUP BY email
HAVING c > 1
ORDER BY c DESC

If you want the full rows:

select * from table where email in (
    select email from table
    group by email having count(*) > 1
)

More Related Contents:

  1. MySQL query finding values in a comma separated string
  2. In MySQL queries, why use join instead of where?
  3. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  4. Can table columns with a Foreign Key be NULL?
  5. MySQL not using indexes with WHERE IN clause?
  6. MySQL dump by query
  7. single fixed table with multiple columns vs flexible abstract tables
  8. How many rows in a database are TOO MANY?
  9. SQLite – ORDER BY RAND()
  10. SQL – How to transpose?
  11. Which SQL query is better, MATCH AGAINST or LIKE?
  12. MySQL equivalent of DECODE function in Oracle
  13. How to delete Duplicates in MySQL table
  14. Best database field type for a URL
  15. Query that ignore the spaces
  16. How to Optimize Queries in a Database – The Basics
  17. MySQL – how to front pad zip code with “0”?
  18. Create date from day, month, year fields in MySQL
  19. what is the difference between GROUP BY and ORDER BY in sql
  20. How do I list all the columns in a table?
  21. Two single-column indexes vs one two-column index in MySQL?
  22. VARCHAR as foreign key/primary key in database good or bad?
  23. How to find all IDs of children recursively?
  24. You can’t specify target table for update in FROM clause
  25. How can I get column names from a table in Oracle?
  26. Alter a MySQL column to be AUTO_INCREMENT
  27. MySQL remove duplicates from big database quick
  28. How can I send email from PostgreSQL trigger?
  29. Check for x consecutive days – given timestamps in database
  30. MySQL skipping first row

Leave a Comment