Selecting Top N Records per Group

When you have multiple rows for let’s say a productID, there will probably be times when you need to select the top n subset of each those productid’s based upon a sort criteria. This is a relatively common TSQL need and there are a couple different methods to do this.

First let’s create some dummy data.

Now that we have our data created, let’s select the top 2 rows for each productid based upon the earliest salesorderid. This salesorderid could actually represent any sequential column in the table, be it a date, or a primary key.

The first method we’ll use is to select using the new window function ROW_NUMBER() introduced in SQL 2005. What we’re doing here is simply creating a numbering system that enumerates each instance of the productid ordered by the salesorderid. We then do another select on this query.

The next method we’ll use is compatible with all versions of SQL. Here we are doing a self-join using a correlated subquery. Meaning we are correlating the productid from the outer query, with the productid from the inner query. We then select the top two salesorderid’s from each instance, constraining our outer result set to the top 2 rows for product.

That’s pretty much it. Personally I prefer using the window functions when I can, they typically have a speed advantage over other methods because they don’t join anything, just sort by doing an order by internally. You may see other methods using a CTE. This is not actually another method, it’s simply the same as the windowed function execution-wise.