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