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
http://www.kitebird.com/
sql, query
