# Top 5 Rows per Group

• ### 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

• 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 Saturday, November 21, 2009 12:42 AM
Friday, November 20, 2009 11:21 PM

### All replies

• Any 5, or do you have a specific order in mind?
Friday, November 20, 2009 10:28 PM
• 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

Friday, November 20, 2009 10:33 PM
• 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 Saturday, November 21, 2009 12:42 AM
Friday, November 20, 2009 11:21 PM
• 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.

Friday, November 20, 2009 11:40 PM
• 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
• What approach would you use for this problem in SQL Server 2000?

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,
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
• 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
• Hi Sam,

The example uses  ROW_NUMBER() OVER(PARTITION BY

Sunday, November 22, 2009 6:50 PM