Archive for April 3, 2017

SQL Trivia – Return every Nth row from Table or a result set

April 3, 2017 2 comments

So few days back I got a ping from one of my reader, he was asked one question in a SQL Interview and he had a hard time to answer that:

How do you return every Nth row from Table or a result set?

He told he knew how to get top 2nd or top Nth record from a table, but was not able to come up with the logic for this problem.

I told him this can be done easily done by using Modulus “%” (percentage) operator.

–> Below is the simple logic by using Modulus logic to get consecutive 5th position in the below record-set. Below every row with “0” value (highlighted yellow) is the 5th consecutive position:

select BusinessEntityID % 5 AS [5thPosition], * 
from [Person].[Person]


–> And by simply moving the above Modulus logic to the WHERE clause will give to filtered rows:

select * 
from [Person].[Person]
where BusinessEntityID % 5 = 0

Categories: SQL Trivia