FAQ - General

SQL How to return the top N rows in each group

There is only a very little (set) magic here!
The solution is a sub query.
The first thing to remember is that the query values are visible to the sub query.

So if we just simply did a simple select statement then we will just get all the rows:

SELECT ItemGroupingID, ItemID, Product, PercentAch FROM Items

If we just arrange the ItemID in ItemSortMeasure

SELECT TOP 5 ItemID FROM Items as S ORDER BY ItemSortMeasure DESC

We will just get 5 rows.

However query values are visible to the sub query

So we can filter the sub query for each ItemGroupingID

WHERE S.ItemGroupingID = Items.ItemGroupingID

So the where clause in the query now on checks if the item is in the top 5 for sub query of the group of its ItemGroupingID

SELECT ItemGroupingID, ItemID, ItemName, ItemSortMeasure
( SELECT TOP 5 ItemID FROM Items as S WHERE S.ItemGroupingID = Items.ItemGroupingID ORDER BY ItemSortMeasure DESC )

You could ofcourse expand this out using a cursor and its a good exercise I'll leave this to you to do, however Cursors are heavy users of resources and MS can probably do some magic if you have a good index and make the query fly.

So you could run both cursor and non cursor version in Query analyser and look at the exectution plans. Another exercise for the reader!

If you found this information useful, please share it!

This FAQ was last updated on Friday, November 2, 2018

Contact Information

To find out more about Ava solutions you can contact us in a number of ways:
Follow Us...