If you’re getting an error that reads “A TOP can not be used in the same query or sub-query as a OFFSET” when running a query in SQL Server, it’s probably because you’re using the TOP() clause and the OFFSET clause in the same query or sub-query.

We can’t use the TOP() clause and the OFFSET clause in the same query in SQL Server.

Below are three options for fixing this error.

Example of Error

Here’s an example of code that produces the error:

SELECT
    TOP(3) 
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId
OFFSET 2 ROWS;

Result:

Msg 10741, Level 15, State 2, Line 7
A TOP can not be used in the same query or sub-query as a OFFSET.

Here, I included the TOP() clause and the OFFSET clause in the same query, but SQL Server returned an error.

As the message states, we can’t use the TOP() clause in the same query or sub-query as the OFFSET clause.

Solution 1

The easiest way to fix this problem is to use a FETCH clause instead of the TOP() clause.

Therefore, we could rewrite the previous query as follows:

SELECT
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId
OFFSET 2 ROWS
FETCH NEXT 3 ROWS ONLY;

Result:

ProductId  ProductName          ProductPrice
---------  -------------------  ------------
3          Long Weight (blue)   14.75       
4          Long Weight (green)  11.99       
5          Sledge Hammer        33.49       

3 row(s) returned

This time I got the result I was looking for. The query results were limited to just the first three rows (after skipping the number of rows specified by the offset).

Solution 2

Another way to resolve this issue is to remove the OFFSET clause. This solution will only make sense if you don’t actually want to skip any rows.

In our case, we can simply remove the last line from our original query:

SELECT
    TOP(3) 
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId;

Result:

ProductId  ProductName               ProductPrice
---------  ------------------------  ------------
1          Left handed screwdriver   25.99       
2          Right handed screwdriver  25.99       
3          Long Weight (blue)        14.75       

3 row(s) returned

So our result set is limited to just three rows as specified, but we can see that it starts at the beginning of the result set without skipping any rows. Therefore, we get rows 1 through 3, as opposed to rows 3 through 5 when using the previous solution.

Solution 3

It’s also possible to set a row count limit by using the SET ROWCOUNT option. This option allows us to specify how many rows are returned by all subsequent SQL queries.

When we use this option, we can use the OFFSET clause in our queries without getting an error.

So, here’s how we can change our original SQL query to use this solution.

First, we run the following command:

SET ROWCOUNT 3;

That sets the row count limit to three rows. Now, all subsequent queries will adhere to that limit.

Now, all we need to do is drop the TOP() clause from our query, but we can leave the OFFSET clause:

SELECT
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId
OFFSET 2 ROWS;

Result:

ProductId  ProductName          ProductPrice
---------  -------------------  ------------
3          Long Weight (blue)   14.75       
4          Long Weight (green)  11.99       
5          Sledge Hammer        33.49       

3 row(s) returned

As expected, three rows are returned, after skipping the first two rows.

Don’t forget that this option affects all subsequent queries. If you forget to reset the ROWCOUNT once you’re done, future queries could return incomplete results without you even knowing it. So it’s probably a good idea to reset the ROWCOUNT once you’re finished with the query/queries that you needed it for.

To reset it, simply set it to zero:

SET ROWCOUNT 0;

After running that code, subsequent queries will return all rows without limitation (unless of course, they’re limited by one of the other methods).