How does one select EVERY Nth row from a table?

Question

Post reply

How does one select EVERY Nth row from a table?

Arun-174141

Old Hand

Points: 328
More actions
July 5, 2006 at 12:33 am

#171508

Hi Friends,
I want to retrive each nth row from a table in sql server 2000.
Example: if i give n as 5 then
5,10,15,20,........... rows should be returned.
Thanks
Arun

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 1

Do you have a unique identity in the table?

Then use

DECLARE @NthRow INT

SELECT @NthRow = 5

SELECT *

FROM YourTable

WHERE YourIdentityColumn % @NthRow = 0

AND YourIdentityColumn > 0 -- May not be necessary if Identity starts with 1.

Good luck!

N 56°04'39.16"
E 12°55'05.25"

Arun-174141 Old Hand Points: 328 More actions · Answer 2

Hi Peter,

I don't have unique Identity on this because what I am using is a view. Which is formed by four tables.

Thanks for your quick replay

Arun

Phil Factor SSC-Insane Points: 20244 More actions · Answer 3

You'd then need to insert the results from the view (in the order you need) into a temporary table or a table variable that has an identity column, and then use Peter's trick from that table rather than the original view.

Best wishes,
Phil Factor

stax68 SSChampion Points: 11711 More actions · Answer 4

If you literally want precisely every Nth row, then you would also need to do something similar to Ryan's suggestion if the table with the identity column has had deletions.

If you just want to get a pseudorandom sample of approx. 20% of the rows, you could use something like

select *
from
where

If you have wide columns like varchar(8000) in the table, you might want to exclude these from the checksum for performance reasons. In the example below I've excuded the wide varbinary and nvarchar columns from syscomments. The example also uses abs() and group by to show (well, suggest!) that the distribution isn't skewed.

select

abs(checksum(id, number, colid, status, texttype, language, encrypted, compressed)) % 5 [hash], count(*) [rowcount]

from

syscomments

group

by abs(checksum(id, number, colid, status, texttype, language, encrypted, compressed)) % 5

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

theostat SSC Enthusiast Points: 152 More actions · Answer 5

theostat

SSC Enthusiast

Points: 152

May 16, 2017 at 2:09 pm

#1942789

to Tim Wilkinson: Kant never said that

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

theostat - Tuesday, May 16, 2017 2:09 PM
to Tim Wilkinson: Kant never said that

Please note that was posted 11 years ago and that user has not even logged in for over 3 years.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

stax68 SSChampion Points: 11711 More actions · Answer 7

It is obvious that between theory and practice there is required, besides, a middle term connecting them and providing a transition from one
to the other, no matter how complete a theory may be; for, to a concept of the understanding, which contains a rule, must be added an act of judgment by which a practitioner distinguishes whether or not something is a case of the rule; and since judgment cannot always be given yet another rule by which to direct its subsumption (for this would go on to infinity), there can be theoreticians who can never in their lives become practical because they are lacking in judgment, for example, physicians or jurists who did well during their schooling but who are at a loss when they have to give an expert opinion. But even where this natural talent is present there can still be a deficiency in premises, that is, a theory can be incomplete and can, perhaps, be supplemented only by engaging in further experiments and experiences, from which the recendy schooled physician, agriculturalist, or economist can and should abstract new rules for himself and make his theory complete. In such cases it was not the fault of theory if it was of littie use in practice, but rather of there having been not enough theory, which the man in question should have learned from experience and which is true theory even if he is not in a position to state it himself...

https://hesperusisbosphorus.files.wordpress.com/2015/02/theory-and-practice.pdf

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

theostat SSC Enthusiast Points: 152 More actions · Answer 8

theostat

SSC Enthusiast

Points: 152

May 17, 2017 at 12:54 pm

#1942996

Thank you Tim, looking into it.