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:
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
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
So keep experimenting.