How to get Top nth Record of a Group or Category from SQL Server Table

For reporting purposes some times we need to grouping data. Not only that we need to show group wise Top n Records. Normally displaying TOP n records of a SQL server table is an easy job but if we need to apply TOP clause on each group or category then it will be a difficult job. That’s why in this article I will discuss about “How to show Group Wise Top n Records“.

To do the Example I am using the below table:
tblstock

Get Category Wise Top N Rows for SQL server 2005, 2008 & 2012:
SQL server 2005 & above provides us ROW_NUMBER() function. By using this function we can numbering the records in a Group or category basis. See the Query & Output from below:

SELECT ROW_NUMBER() OVER(PARTITION BY BrandName ORDER BY PhysicalQuantity DESC) AS RowNumber,
*
FROM tblStock

Output:
Group Wise Top n Rows

Now if we want to pick Group wise highest quantity product from the table then we can easily use the RowNumber column. If we use RowNumbe<2 then we will get Group wise highest quantity product. If we want Group wise nth record then the condition will be "RowNumber<n+1". So now check the Group wise highest quantity product:

SELECT BrandName,CategoryName,ProductName,PhysicalQuantity FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY BrandName ORDER BY PhysicalQuantity DESC) AS RowNumber,
*
FROM tblStock) TS
WHERE TS.RowNumber<2

Output:
Group Wise Top n Rows 2

So keep experimenting.

Posted in Database, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*