CODEWITHSUNDEEP
php
Humza Khan
Humza Khan

Reputation: 733

PHP MySQLi how to get the most repeated value from a table

There's a little thing I'm stuck with. I've a table in my database like:

id | AuthorId | Book 
---------------------
1  |    2     | book name
2  |    2     | book name
3  |    2     | book name
4  |    2     | book name
5  |    5     | book name
6  |    5     | book name
7  |    8     | book name
8  |    9     | book name
9  |    9     | book name
10 |    6     | book name

As you can see, Author ID "2" is repeated the highest times in this table ( 4 times ) while the author Author IDs appeared less then 4 times. How can I get the ID "2" from this table using php MySQLi, which is the most frequently occurring value in this data? I've no idea how can I do this.

Thank you very much.

Upvotes: 0

Views: 771

Answers (2)

ron
ron

Reputation: 826

Try this

select AuthorId , count(AuthorId ) as max
from table_name 
group by AuthorId order by max desc limit 1;

The order by max desc is for ordering the max value at the first row. The limit 1 is from getting only the first row

Upvotes: 1

geoandri
geoandri

Reputation: 2428

Try the following query. You count rows grouped by AuthorID DESC , and limiting the result to the top value.

SELECT AuthorId, COUNT(AuthorId) as c from table
GROUP BY AuthorId
ORDER BY COUNT(AuthorId) DESC
LIMIT 1;

Upvotes: 2

Related Questions