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

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 开发者_JAVA百科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.

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

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

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