2011-05-09 15:54



I have a mysql database with

  • table 'product' containing columns 'product_id', 'name', 'price'
  • table 'review' containing columns 'review_id', 'product_id', 'review_content'

I want to retrieve results for 'product_id' = 123, but sort if by number of reviews. I am able to find the number of reviews for a particular product using COUNT('review_id'), but how do I sort the results by number of reviews?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答


  • duan6832168 duan6832168 10年前

    Since you're presumably selecting COUNT('review_id') in your query, you can simply add ORDER BY COUNT('review_id') DESC at the end.

    点赞 评论 复制链接分享
  • dq_609586475 dq_609586475 10年前

    If you want the products with the most reviews first...

    SELECT P.product_id, 
           IFNULL(COUNT(R.product_ID),0) as "Reviews"
    FROM product as P
    LEFT JOIN review as R ON P.product_ID = R.product_id
    GROUP BY P.product_id
    ORDER BY Review DESC;

    ...otherwise switch DESC with ASC.

    点赞 评论 复制链接分享
  • doujing2017 doujing2017 10年前

    To save doing the count twice use: count('review_id') as num_reviews and then order by num_reviews

    点赞 评论 复制链接分享
  • doujionggan9570 doujionggan9570 10年前

    Try this to get all the products and the number of reviews on each:

    SELECT P.*, IFNULL(COUNT(R.product_ID),0) AS NumReviews
    FROM Product AS P
    LEFT JOIN Review AS R ON R.product_id = p.product_id
    ORDER BY  COUNT(R.product_ID) DESC
    点赞 评论 复制链接分享