Geek Answers Handbook

Choose the top 10 with the highest average score

Say I have a table of products and scores.

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 application to choose?

here is mine that 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
+4
source share
3 answers

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 that I am not using TOP is because it will not process a recurring record with the highest average value. But instead, you can use TOP WITH TIES .

+2
source

It can 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
+4
source

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 the table name and number of rows.

+2
source

More articles:


All Articles