Advertisements
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:

Ho 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


Advertisements
Categories: SQL Trivia
  1. No comments yet.
  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 )

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 )

w

Connecting to %s

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

%d bloggers like this: