CODEWITHSUNDEEP
mysql
Jared
Jared

Reputation: 1

MySQL Select Top 10 Most Recurring Field Values

I need to query my DB to return the top 10 most common values in a column. I am using this right now:

$most_popular = (' 
SELECT item_id 
FROM   active_members 
WHERE  component = "members" 
       AND type = "has_picture" 
GROUP  BY item_id 
HAVING Count(*) = (SELECT Count(*) 
                   FROM   wp_ms_activity 
                   GROUP  BY item_id 
                   ORDER  BY Count(*) DESC 
                   LIMIT  1)

 ' );

This only returns ONE value though, and its slow. Is there a better way? I tried changing the limit 1 to 10, and it says "subquery returns more than one value".

Upvotes: 0

Views: 201

Answers (1)

juergen d
juergen d

Reputation: 204854

Just order by the count of the records per item_id

SELECT item_id 
FROM active_members 
WHERE component = "members" 
AND type = "has_picture" 
GROUP BY item_id 
order by count(*) desc
limit 10

Upvotes: 1

Related Questions