Select most common value from a field in MySQL

by

You need to group by the interesting column and for each value, select the value itself and the number of rows in which it appears.

Then it’s a matter of sorting (to put the most common value first) and limiting the results to only one row.

In query form:

SELECT column, COUNT(*) AS magnitude 
FROM table 
GROUP BY column 
ORDER BY magnitude DESC
LIMIT 1

More Related Contents:

  1. When I want to install MySQL I faced this issue “root account password” I forgot it
  2. Restoring MySQL database from physical files
  3. MySQL Trigger after update only if row has changed
  4. MySQL “WITH” clause
  5. MySQL Query – Records between Today and Last 30 Days
  6. MySQL “Group By” and “Order By”
  7. MySQL Query to select data from last week?
  8. Basics of Foreign Keys in MySQL?
  9. What is the benefit of zerofill in MySQL?
  10. MySQL user DB does not have password columns – Installing MySQL on OSX
  11. Migrate from Oracle to MySQL
  12. #1139 – Got error ‘repetition-operator operand invalid’ from regexp
  13. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  14. MySQL Data Source not appearing in Visual Studio
  15. MySQL JSON: How to find object from key value
  16. MYSQL into outfile “access denied” – but my user has “ALL” access.. and the folder is CHMOD 777
  17. Advanced MySql Query: Update table with info from another table
  18. SQL unique varchar case sensitivity question
  19. Commit data in a mysql container
  20. How do I count columns of a table
  21. Mysqldump launched by cron and password security
  22. MySQL Trigger cannot update table – getting ERROR 1442
  23. How to insert Hindi language in Mysql
  24. MySQL delete duplicate records but keep latest
  25. Why MySQL is giving error “Not allowed to return a result set from a function”?
  26. Where I can find a list of “mysqldump” exit codes?
  27. Correct way to keep pooled connections alive (or time them out and get fresh ones) during longer inactivity for MySQL, Grails 2 app
  28. MySQL: Setting sql_mode permanently
  29. Is it possible to count two columns in the same query
  30. mySQL regex in the where clause

Leave a Comment