Sql Server

Question

Kindly anyone suggest the following query is the best way to get the top 10 records from each group, in the performance wise.

SELECT * FROM 
(
SELECT PrdVarProductType,ROW_NUMBER() OVER (PARTITION BY PrdVarProductType ORDER BY PrdVarProductType,PrdDteCreateDate DESC)AS RowNumber 
FROM dbo.KKVIPrdMSt 
WHERE PrdChrActiveYN='Y'
)AS T
WHERE RowNumber<=10
ORDER BY PrdVarProductType

Thanks in Advance,

Prathees

Answer 1

Performance depends on how the Indexes have been defined for the table KKVIPrdMst....We cannot talk about the performance aspect by looking at the query alone.

---

Murali Krishnan

Answer 2

Yep, this is one way, another one  would be using CROSS APPLY operator, see example 

SELECT S.SupplierID, S.CompanyName, CA.ProductID, CA.UnitPrice
FROM dbo.Suppliers AS S
  CROSS APPLY
    (SELECT TOP (10) *
     FROM dbo.Products AS P
     WHERE P.SupplierID = S.SupplierID
     ORDER BY UnitPrice DESC, ProductID DESC) AS CA
ORDER BY S.SupplierID, CA.UnitPrice DESC, CA.ProductID DESC;

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

Thank you Murali Krishnan,

This is the right way to retrieve the top 10 records from each group or give your suggestion.

 

Answer 4

Yes.. That would work. You can use CTE as well.

WITH CTE
AS
(
SELECT PrdVarProductType,ROW_NUMBER() OVER (PARTITION BY PrdVarProductType ORDER BY PrdVarProductType,PrdDteCreateDate DESC)AS RowNumber 
FROM dbo.KKVIPrdMSt 
WHERE PrdChrActiveYN='Y'
)
SELECT * FROM CTE WHERE RowNumber <= 10

---

Murali Krishnan

Answer 5

Please check these blogs that explain this problem from performance point of view

Optimizing TOP N per Group Queries - blog by Itzik Ben-Gan explaining various optimization ideas
Including an Aggregated Column's Related Values - Erik and mine blog presenting several solutions of the problem with explanations for each
Including an Aggregated Column's Related Values - Part 2 - my blog post with use cases for the previous blog

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog