Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

ROW_NUMBER and TOP

  • 28 January 2013
  • Author: Kathi Kellenberger
  • Number of views: 13568
  • 0 Comments

I wanted to point out some behavior to watch out for when you are working the ROW_NUMBER function along with TOP.  Here is a query on the SalesOrderHeader table in AdventureWorks along with the ROW_NUMBER function:

SELECT ROW_NUMBER() OVER(Order By CustomerID) RowNum, CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader;

I am ordering the row numbers by the CustomerID, and the data just happens to be returned by the CustomerID as well. Since I am not using an ORDER BY on the query itself, SQL Server does not guarantee the order. Here are the first few rows returned:


 
In the next example I’ll add the TOP operator and an ORDER BY clause to query:


SELECT TOP(5) ROW_NUMBER() OVER(Order By CustomerID) RowNum,
 CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY CustomerID;

Since the CustomerID is not unique in my results, I would probably want to add the SalesOrderID to the ORDER BY clause of the ROW_NUMBER and the query. In this case, the result set looks like this:

What happens while using TOP if the two ORDER BY clauses do not match? Remember that the ORDER BY clause in a WINDOW function does not depend at all on the ORDER BY clause for the query.

SELECT TOP(5) ROW_NUMBER() OVER(Order By CustomerID) RowNum,
 CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY CustomerID DESC;

In this result set, instead of the row numbers returned beginning with one, I get different results:


 
The reason for this is that the ROW_NUMBER function is applied to the results before the TOP operator is applied. And, in this case, the row numbers are applied in ascending order, but the result set is in descending order.

The next example uses the CustomerID for the row number ORDER BY and the SalesOrderID for the query ORDER BY, both in ascending order.


SELECT TOP(5) ROW_NUMBER() OVER(Order By CustomerID) RowNum,
 CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;

Notice that the results may not be what you expected:

 

If the two ORDER BY clauses are identical, the row numbers returned will start at one. In this query, both ORDER BY clauses are CustomerID desc plus SalesOrderID.

SELECT TOP(5) ROW_NUMBER() OVER(Order By CustomerID DESC, SalesOrderID) RowNum,
 CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY CustomerID DESC, SalesOrderID;

Now that the ORDER BY clauses match, the row numbers returned starts at one.  Since the combination of CustomerID and SalesOrderID is unique, the row numbers will always be applied the same for this data.


If you need to use TOP along with ROW_NUMBER or one of the other ranking functions (RANK, DENSE_RANK and NTILE) make sure you pay attention to the row numbers that are returned.  You may need to tweak one of the ORDER BY clauses to get the results you need. 

 

Print
Categories: SQL Server
Tags:
Rate this article:
No rating

Please login or register to post comments.