example table data:

id | name | tool
--------------------
 1 | bob  |   scissor
 2 | mike |   knife
 3 | john |   thread
 4 | joe  |   ruler
 5 | kim  |   marker
 6 | dean |   board
 7 | paul |   knife
 8 | john |   scissor
 9 | kim  |   ruler
 10| mike |   scissor
 11| mike |   board
 12| joe  |   board
 13| paul |   scissor
 13| jake |   marker

and would like to get the top 5 most common from the column 'tool' which would tell me something like

1 - scissor (4)
2 - board (3)
3 - knife (2)
4 - ruler (2)
5 - marker (2)

CodePudding user response：

You can count the tools then order by the count limiting to 5.

select tool,count(tool) as nr_count
from test
group by tool
order by nr_count desc limit 5;

Result:

tool    nr_count
scissor 4
board   3
knife   2
ruler   2
marker  2

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4c95c05c966d262547f752a2cb599b3b

Note that you still have another tool with count of 2 results the above query doesn't take in consideration. You need to apply another order by