Question: Lets say, I have Product and Score tables. 

```
Product
-------
id
name

Score
-----
id
ProductId
ScoreValue

```

I want to get the top 10 Products with the highest AVERAGE scores, how do I get the average and select the top 10 products in one select statement?

here is mine which selects unexpected rows

```
SELECT TOP 10 Product.ProductName Score.Score 
FROM Product, Score 
WHERE Product.ID  IN (select top 100  productid  
                       from score 
                       group by productid 
                       order by sum(score) desc) 
order by Score.Score desc

```

Answer: 
Give this a try,

```
WITH records
AS
(
    SELECT  a.ID, a.Name, AVG(b.ScoreValue) avg_score,
            DENSE_RANK() OVER (ORDER BY AVG(b.ScoreValue) DESC) rn
    FROM    Product a
            INNER JOIN Score b
                ON a.ID = b.ProductID
    GROUP   BY a.ID, a.Name
)
SELECT  ID, Name, Avg_Score
FROM    records
WHERE   rn <= 10
ORDER   BY avg_score DESC

```

The reason why I am not using `TOP` is because it will not handle duplicate record having the highest average. But you can use `TOP WITH TIES` instead.
This might do it

```
SELECT TOP 10 p.ProductName, avg( s.Score ) as avg_score
FROM Product p
    inner join Score s on s.product_id = p.product_id
group by p.ProductName, p.product_id
order by avg_score desc

```