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

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

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
  1. Bili
    February 12, 2019 at 7:47 pm

    It doesnt cover special test case when some BusinessIdentities were removed. E.g. remove 3, 4 then listed identity 5 will not be 5th in the list anymore.

    • February 12, 2019 at 9:10 pm

      Good catch @Bili. In this case you can add a calculated column by using ROW_NUMBER() operator to provide sequential numbers to all rows, then apply this logic.

  1. No trackbacks yet.

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 )

Connecting to %s

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

%d bloggers like this: