Skip to content
Advertisement

how to query the max value of count

I try to get the max count value but error

this is my data

username   tool_id
___________________
user1         1
user1         2
user2         1
user3         3

I need to get the max count of tool_id

I use this code to get tool_count

SELECT tool_id, COUNT(tool_id) AS tool_count 
  FROM table 
 GROUP BY tool_id
tool_id   tool_count
_____________________
  1          2
  2          1
  3          1

and I use

SELECT tool_id,max(tool_count) 
 FROM
 (
  SELECT tool_id, COUNT(tool_id) AS tool_count 
    FROM table 
   GROUP BY tool_id
 )

to get max tool_count but it error

the result I need :

tool_id  tool_count
__________________
   1         2

Advertisement

Answer

You can try using order by desc with limit 1

SELECT tool_id, COUNT(tool_id) as tool_count 
from table 
group by tool_id
order by tool_count desc limit 1
4 People found this is helpful
Advertisement