Retrieving multiple rows in SQL Server but distinct filtering only on one

Question

I have this:

SELECT Product.ProductID, Product.Name, Product.GroupID, Product.GradeID,
       AVG(tblReview.Grade) AS Grade
FROM   Product left Join tblReview ON Product.GroupID = tblReview.GroupID
WHERE  (Product.CategoryID = @CategoryID)
GROUP  BY Product.ProductID, Product.Name, Product.GroupID, Product.GradeID

I would like to return only the rows where Product.Name is unique. If I make a SELECT DISTINCT the ProductID is different on every row so all the rows are unique.

Answer 1

You could do something like this (see below), but this assumes that you don't care if 2 products with the same name have a different GroupID, etc. because you can't really list those unless you use a different approach (multiple queries).

SELECT        Product.Name
            , max(Product.ProductID) as ProductID
            , max(Product.GroupID) as GroupID
            , max(Product.GradeID) as GradeID,
            , AVG(tblReview.Grade) AS Grade
FROM            Product left Join tblReview ON Product.GroupID = tblReview.GroupID

WHERE        (Product.CategoryID = @CategoryID)

GROUP BY Product.Name

HAVING COUNT(distinct Product.Name) = 1

Answer 2

SELECT  p.ProductID, p.Name, p.GroupID, p.GradeID,
        (
        SELECT  AVG(grade)
        FROM    tblReview r
        WHERE   r.GroupID = p.GroupID
        ) AS Grade
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY name ORDER BY productID) AS rna,
                ROW_NUMBER() OVER (PARTITION BY name ORDER BY productID DESC) AS rnd
        FROM    Product
        WHERE   CategoryID = @CategoryID
        ) p
WHERE   rna = rnd

Answer 3

Should this work for you?

SELECT Product.ProductID, Product.Name, Product.GroupID, Product.GradeID, 
       AVG(tblReview.Grade) AS Grade 
FROM   Product left Join tblReview ON Product.GroupID = tblReview.GroupID 
WHERE  (Product.CategoryID = @CategoryID) 
GROUP  BY Product.ProductID, Product.Name, Product.GroupID, Product.GradeID 
HAVING COUNT(Product.Name)=1