How to Skip Top N Rows in SQL Server? – Interview Question of the Week #237

Question: How do you skip the first N rows and return the rest? Use ORDER BY with OFFSET N ROWS. FETCH is optional.

I asked this in an interview and heard solutions using ranking functions, temporary tables, CTEs and subqueries. Nobody mentioned OFFSET. Those alternatives can work, but this particular requirement has a very short answer from SQL Server 2012 onward.

SELECT *
FROM AdventureWorks2025.Person.Address
ORDER BY AddressID
OFFSET 100 ROWS;

-- Return just the next page instead of every remaining row.
SELECT AddressID, AddressLine1
FROM AdventureWorks2025.Person.Address
ORDER BY AddressID
OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

The first query is the original AdventureWorks Address example, with the installed sample database name updated. It skips 100 rows in AddressID order, then returns every remaining row. The second adds a ten-row page. OFFSET is a row count, not a test that AddressID is greater than 100: gaps in IDs don’t change that distinction.

Use a unique ordering, such as the AddressID key, so ties don’t make the selected boundary arbitrary. Separate requests can still move rows between pages if the underlying data changes.

I like the simplicity of this syntax, but it is not always faster than every alternative. A large offset can require reading and discarding many earlier rows. For deep paging, compare the plan and I/O with a key-based continuation method when the application permits it.

• Retrieving N rows after ordering
• Paging in different SQL Server versions
• Pagination, interview question #111

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.