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
Advertisements
This entry was posted in Programming and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s