how to get maximum rating of product in different category in ssrs-CodePudding

I have one product table which contains ratings for different products. I want to see the product details of the product with the highest rating in each category in SSRS. I tried

[select distinct category, ProductName, rating from product where rating in (select max(rating) from Product) ].

I got the result but for one of the category i.e girls, the maximum rating for any product is 4.9. So, the query is not showing output for the girl's category. Please help. I have attached the product table for reference.

CodePudding user response：

You are selecting max(rating) from Product table, and then selecting records matching to that rating, as seen in this image, I see 5.0 as max rating across all the categories, since you mentioned for the girl's category, max(rating) is 4.9 it will never match the max(rating) you are fetching. maybe if tried grouping your max(rating) by category, then you will get girl's category in your output

CodePudding user response：

Assuming you want to get the product or products that have the highest rating in each group then try something like this.

SELECT Category, ProductName, rnk 
    FROM (SELECT *, RANK() OVER(PARTITION BY Category ORDER BY Rating DESC) as rnk
                      FROM Product
            ) a
    WHERE rnk = 1
        AND Category = @Category

The inner query simply adds rnk column which gives us the rank within each category based on the Rating column. The outer query selects from this inner query returning anything with a rnk of 1. If two or more products share the same ranking in a single category, both will be shown.

Edit after comment from OP: Add Category = @myCategory to the WHERE clause.

This assumes that the SSRS parameter name called myCategory. If not then change the code to match the parameter name. This name is case sensitive.