Sort MYSQL Data By Number of Entries

Question

Hi, I'm wondering how I can sort mysql data based on the number of entries.

I'm doing this so I can have a page of the top purchases, so it would have to retrieve all the product_id's from a table, and then sort them by the most times one shows up, limiting it to 10 or something.

Thanks!

Answer 1

Use:

  SELECT p.product_id,
         COUNT(*) AS num_orders
    FROM PRODUCTS p
GROUP BY p.product_id
ORDER BY num_orders DESC --to put highest sales at the top of the list
   LIMIT 10 -- Query will return 10 records, max