Get MAX from GROUP BY

Question

I coached some SQL when it hit me. I wanted to see how many times a product appeared, and from there it turns out the product that came up most.

This shows how many times each product appears:

mysql> SELECT commodity, COUNT(commodity) count FROM orders GROUP BY commodity ORDER BY count; +----------------------+------------+ | commodity | count | +----------------------+------------+ | PERSIAN MELON | 4 | | BEANS | 6 | | CASABA | 10 | | ASPARAGUS | 11 | | EGGPLANT | 12 | | TOMATOES, CHERRY | 16 | | GALIA MELON | 18 | +-----------------------------------+ 

I am trying to get the row with the highest value, but all this is wrong:

 mysql> SELECT commodity, MAX(COUNT(commodity)) count FROM orders GROUP BY commodity ORDER BY count; 

What is the right way to do this?

Answer 1

CAUTION: the request will not process duplicate records with a maximum COUNT value

 SELECT commodity, COUNT(commodity) `count` FROM orders GROUP BY commodity ORDER BY `count` DESC LIMIT 1 

But it will be,

 SELECT commodity, COUNT(commodity) `count` FROM orders GROUP BY commodity HAVING COUNT(commodity) = ( SELECT MAX(`COUNT`) FROM ( SELECT COUNT(commodity) `count` FROM orders GROUP BY commodity ) s ) 

Answer 2

Try this request

  SELECT commodity,COUNT(commodity) AS count FROM orders GROUP BY commodity ORDER BY count desc LIMIT 1; 

Answer 3

I would write:

 select * from (SELECT commodity, COUNT(commodity) count FROM orders GROUP BY commodity ORDER BY count desc) where rownum <2; 

Answer 4

This is normal, just add desc to order

 SELECT commodity, COUNT(commodity) count FROM orders GROUP BY commodity ORDER BY count DESC; 

the first row will have a maximum value and add a limit to get only one entry

 SELECT commodity, COUNT(commodity) count FROM orders GROUP BY commodity ORDER BY count DESC LIMIT 1; 

Answer 5

You seem to be doing it right. With the exception of ORDER BY arranges them in ASC order. make it down

 mysql> SELECT commodity, COUNT(commodity) count FROM orders GROUP BY commodity ORDER BY count DESC;