Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Sql Server

Answered Sql Server

  • Monday, April 30, 2012 5:46 AM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Monday, April 30, 2012 5:51 AM
     
     
    Sign In to Vote
    0
    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

     
  • Monday, April 30, 2012 5:55 AM
    Answerer
     
     
    Sign In to Vote
    0

    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/

     
  • Monday, April 30, 2012 5:56 AM
     
     
    Sign In to Vote
    0

    Thank you Murali Krishnan,

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

     

     
  • Monday, April 30, 2012 6:00 AM
     
      Has Code
    Sign In to Vote
    0

    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

     
  • Monday, April 30, 2012 6:12 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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