none
Top 5 Rows per Group RRS feed

  • Question

  • Hi Everyone,

    I need to write a query that returns the top 5 Serial Numbers per Brand and Merchandise Code.  So each Brand will have 5 rows per Merchandise Code.  How can I accomplish this?

    My Tables
    Assets table = AssetID, ProductID, SerialNumber
    Products table = ProductID, ModelNumber, Brand, MerchandiseCode, Description


    Thank You
    -Sam

    Friday, November 20, 2009 10:19 PM

Answers

  • It is not clear the relation between the tables (1:1, 1:M, etc.) and the order for the top serial number. Assuming it is SerialNumber, here is another solution:

    SELECT ProductID, ModelNumber, Brand, MerchandiseCode, SerialNumber
    FROM (
    SELECT P.ProductID, P.ModelNumber, P.Brand, P.MerchandiseCode, 
           A.SerialNumber,
           ROW_NUMBER() OVER(PARTITION BY P.Brand, P.MerchandiseCode
                             ORDER BY A.SerialNumber) AS rk
    FROM Products AS P
    JOIN Assets AS A
      ON P.ProductID = A.ProductID) AS T
    WHERE rk <= 5;

    Plamen Ratchev
    • Marked as answer by SamCosta Saturday, November 21, 2009 12:42 AM
    Friday, November 20, 2009 11:21 PM
    Moderator

All replies

  • Any 5, or do you have a specific order in mind?
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Friday, November 20, 2009 10:28 PM
    Moderator
  • In SQL2005 or above, you can use the CROSS APPLY operator:

    SELECT Brand, MerchandiseCode, SerialNumber
    FROM
        (SELECT DISTINCT Brand, MerchandiseCode
         FROM Products) P
    CROSS APPLY
        (SELECT TOP 5 SerialNumber
         FROM Assets
         JOIN Products ON Assets.ProductID=Products.ProductID
         WHERE Brand=P.Brand AND MerchandiseCode=P.MerchandiseCode
         ORDER BY SerialNumber) X


    --Brad (My Blog)
    Friday, November 20, 2009 10:33 PM
    Moderator
  • It is not clear the relation between the tables (1:1, 1:M, etc.) and the order for the top serial number. Assuming it is SerialNumber, here is another solution:

    SELECT ProductID, ModelNumber, Brand, MerchandiseCode, SerialNumber
    FROM (
    SELECT P.ProductID, P.ModelNumber, P.Brand, P.MerchandiseCode, 
           A.SerialNumber,
           ROW_NUMBER() OVER(PARTITION BY P.Brand, P.MerchandiseCode
                             ORDER BY A.SerialNumber) AS rk
    FROM Products AS P
    JOIN Assets AS A
      ON P.ProductID = A.ProductID) AS T
    WHERE rk <= 5;

    Plamen Ratchev
    • Marked as answer by SamCosta Saturday, November 21, 2009 12:42 AM
    Friday, November 20, 2009 11:21 PM
    Moderator
  • FYI... I'm guessing that Plamen's ROW_NUMBER() approach will be much more efficient than the CROSS APPLY approach.

    Both approaches are only available in SQL2005 or beyond.

    --Brad (My Blog)
    Friday, November 20, 2009 11:40 PM
    Moderator
  • In case of large tables where the % of rows per group is low the CROSS APPLY approach can be more efficient.
    Plamen Ratchev
    Saturday, November 21, 2009 1:46 AM
    Moderator
  • What approach would you use for this problem in SQL Server 2000?

    I'm asking because of this thread http://forums.asp.net/t/1495067.aspx
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
  • From the link it is not clear what is the unique key in the table. Assuming that ArticleTitle is unique (it can be replaced with any other unique column), then here is one solution for SQL Server 2000:

    SELECT SectionId AS ArticleId, 
           UserId, 
           SectionId, 
           ArticleTitle, 
           ArticleHeadline, 
           ArticleByline, 
           ArticleSummary, 
           ArticleStory, 
           DatePublished, 
           ArticlePriority, 
           SubmissionDate, 
           ArticlePublished
    FROM Articles AS A
    WHERE ArticlePublished = 1
      AND ArticleTitle IN 
         (SELECT TOP 5 B.ArticleTitle
          FROM Articles AS B
          WHERE B.SectionId = A.SectionId
          ORDER BY B.DatePublished DESC);

    Plamen Ratchev
    Sunday, November 22, 2009 5:30 PM
    Moderator
  • I was thinking about exactly the same solution, but at the last moment using IN somehow confused me and I thought it's not allowed to use IN with correlated subquery.

    BTW, I think we need to use ArticlePublished = 1 condition in subquery as well.

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, November 22, 2009 6:27 PM
    Moderator
  • Hi Sam,


    The example uses  ROW_NUMBER() OVER(PARTITION BY

     

    Sunday, November 22, 2009 6:50 PM