How to get the most occurred database rows with php

Question

I don't know how to phrase this, but I'm trying to get the rows with the most occurrences in the column to display, for example

ID   from_id      to_id
1    1            3
2    1            3
3    1            3 
4    1            3 
5    2            3
6    3            3
7    3            3
8    4            3
9    4            3

I'm trying to get 1,4,3 from the database because it's more frequent, how would I do this? Sorry if it's a bad question, I don't know how to phrase it

How is 2 more frequent than 4? – faintsignal Mar 17 '14 at 23:43 — faintsignal, Mar 17 '14 at 23:43
It's an error, I fixed it – user3339454 Mar 17 '14 at 23:48 — user3339454, Mar 17 '14 at 23:48

score 2 · Accepted Answer · answered Mar 17 '14 at 23:49

This is quite easy, just COUNT(from_id), eg like this

SELECT from_id, COUNT(from_id) AS total FROM your_table 
GROUP BY from_id 
ORDER BY total DESC

You can now modify this, eg LIMIT 10 to get the top 10 or WHERE total > 1 before the GROUP BY to only get rows which occure more than once.

How to get the most occurred database rows with php

1 Answers1