T-SQL – Skip X Number of Rows

There may come a time when you need to skip a certain number of rows while querying a database in SQL Server Management Studio. Here is an example used that I see from time to time:

Original data set:

Say we wanted to skip the first 5 records based on the SSN. We could do the following:

	SELECT *
	FROM ( SELECT cstFirstName, cstLastName, cstEmail, cstGender, cstSSN, cstGuid,
	ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY cstSSN) orderId
	FROM dbo.tblCustomer ) A
	WHERE A.orderId > 5 /* Skip first 5 records */

view raw skipByRowNumber.sql hosted with

by GitHub

A better solution to this is to use the OFFSET clause:

	SELECT cstFirstName, cstLastName, cstEmail, cstGender, cstSSN, cstGuid
	FROM dbo.tblCustomer
	ORDER BY cstSSN /* <--- ORDER BY clause required to use OFFSET */
	OFFSET 5 ROWS /* Skip 5 rows */

view raw skipByOffset.sql hosted with

by GitHub

A catch to this is that you can’t use the TOP clause. If you need to get a TOP number of rows, append the FETCH clause onto OFFSET:

	SELECT cstFirstName, cstLastName, cstEmail, cstGender, cstSSN, cstGuid
	FROM dbo.tblCustomer
	ORDER BY cstSSN /* <--- ORDER BY clause required to use OFFSET */
	OFFSET 5 ROWS FETCH FIRST 3 ROWS ONLY /* <--- You can use FIRST or NEXT interchangeably */