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.
LVL 17
OmniUnlimitedAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
$sql = "SELECT fname, COUNT(fname) AS num FROM my_table GROUP BY fname ORDER BY num DESC";
0
 
Ray PaseurCommented:
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
)
0
 
Jagadishwor DulalBraces MediaCommented:
SELECT colname, COUNT(*) as total
FROM tablename
GROUP BY colname;

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Ray PaseurCommented:
@Jagadishwor Dulal: Have you tested that query?

@OmniUnlimited: I recommend that you test any suggestions before you accept answers!
0
 
OmniUnlimitedAuthor Commented:
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!
0
 
awking00Commented:
select columnname from
(select columnname, count(*) from table group by columnname order by count(*) desc)
limit 1;
0
 
Ray PaseurCommented:
Thanks for using EE and thanks for the points and your kind words!  Best regards, ~Ray
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.