Get every n-th record using SQL Server

First, a few things you should already know:
Modulus Returns the remainder of one number divided by another.
SELECT 32/10 -> 3
SELECT 32%10 -> 2

To return the n-th row only of a table, use:

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = [value of n]

To return every n-th row in a table, use:

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE [row]%[value of n] = 0

Example:

Return every 10th event from an events table:

WITH tableEvents AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY EventDate)) as row,*
    FROM tblEvents
	Where Month(EventDate) = 7)
SELECT * FROM tableEvents WHERE [row]%10= 0
Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.