asked on

Obtain Value of Max Occurrences

Hello Experts!

Who can tell me what the most efficient way of obtaining the value of a column that occurs most often within that column? I need something that does not require much memory or CPU.

In other words, let's say that I have a column in a table with the following values:

1
2
3
2
3
3
1
3
3
1
3
2
3

I need a mysql statement that will give me the answer of "3" for this column as the value that occurs the most times in that column.

Thank you.

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

The ouputs will look something like this:
USING MySQLi_Result::Fetch_Object():
stdClass Object
(
[fname] => Ray
[num] => 2
)

stdClass Object
(
[fname] => Bill
[num] => 1
)

stdClass Object
(
[fname] => John
[num] => 1
)

Jagadishwor Dulal

SELECT colname, COUNT(*) as total
FROM tablename
GROUP BY colname;

Open in new window

Ray Paseur

@Jagadishwor Dulal: Have you tested that query?

@OmniUnlimited: I recommend that you test any suggestions before you accept answers!

OmniUnlimited

ASKER

Ray thank you so much! As always, your genius just shines.

I tested the queries and only yours gave me the results I was looking for.

Have a great day!

awking00

select columnname from
(select columnname, count(*) from table group by columnname order by count(*) desc)
limit 1;

Ray Paseur

Thanks for using EE and thanks for the points and your kind words! Best regards, ~Ray