List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 23 2003 5:43pm
Subject:Re: mysql search for repeated value
View as plain text  
At 16:27 +0100 4/23/03, Steve Mansfield wrote:
>does anyone know how to search for the the most common/repeated 
>value in a table column.
>I have a table thus:
>id    col1    col2        col3      col4
>1       3           9            22        36
>2       3           7            24        40
>3       2          10           31        39
>4       1          9            25         27
>What i need to do is to search each colunm and find the most 
>repeated value, so in col1 it would be "3"
>and in col2 it would be "9"

To display the values in a column, sorted by the number of occurrences of
each value, do something like this:

SELECT col1, COUNT(*) AS n FROM tablename GROUP BY col1 ORDER BY n DESC;

Add LIMIT 1 to get just the first line of the result, but note that
it's possible for more than one value to occur "most often", and LIMIT
1 won't show you that.

Paul DuBois
sql, query
mysql search for repeated valueSteve Mansfield23 Apr
  • Re: mysql search for repeated valuePaul DuBois23 Apr