Select a range of records from a dataset in T-SQL

This is a way to filter out a numbered range using T-SQL.  I.e. if you wanted to select the 100th – 110th row from your dataset.

Firstly use the OVER statement to add row numbers (as ROW_NUM column) to your dataset, as below.

WITH NUMBERED_ROWS
	AS 
		(
		SELECT 
			row_number() OVER (ORDER BY FAULT_NUMBER) AS ROW_NUM,
			FAULT_NUMBER,
			REPORTED_DATE_TIME			
		FROM  property.FAULT   
        )

Now you have the row numbers, these can easily be used to filter out the required rows
SELECT  
	ROW_NUM,
	FAULT_NUMBER, 
	REPORTED_DATE_TIME
FROM NUMBERED_ROWS 
WHERE ROW_NUM >= 100 and ROW_NUM <= 110