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.
Please try:

```
declare @Product as table (id int, name nvarchar(20))
declare @Score as table (id int, ProductID int, ScoreValue decimal(23, 5))

insert into @Product values (1, 'a'), (2, 'b'), (3, 'c')

insert into @Score values (1, 1, 25), (2, 1, 30), (3, 2, 40), (4, 2, 45), (5, 3, 3)

select 
    distinct top 2 name, 
    ProductID, 
    AVG(ScoreValue) over (partition by name) 
from @Product a inner join @Score b on a.id=b.ProductID
order by 3 desc

```

Change with your table name and number of rows accordingly.