nth subset of data from current table

[agron]

Hi,

I was wondering if you guys have any nth script to reads from table
and outputs into a temp table subset of records. There was a nth tool
I used to use it was GROUP1 which was written in C and it used to be
very fast on nth -in a flat file. In this program we used to pass few
paramaeters. For example if I want 30,000 records from the file of
500,000. The function seams to work something like this. you divide
the 30,000 records of 500,000 which will result with .090909090909.
Now we would pass only the first 7 digit (0909090) as parameter that
would nth the file down to 30,000 records. This function allways
worked whichever number you use as long as the read file is larger
than output file
I like to use the similar concept in Sql Server and I was wondering if
anyone has any script to do this or how to go about this?

Thank you. I appreciate your feedback
agron

[David Portas]

I assume this is sampling for the purposes of generating test-data or
something similar? As we know, tables have no inherent order so this only
makes sense if you have some column that defines a unique, numeric sequence.
If you have such a column then you can use the modulo operator to sample
from it:

SELECT *
FROM YourTable
WHERE col % @m = 0

where @m = (total number of rows) / (required sample size)

Usually however, it's more convenient just to take a random sample of N rows
like this:

SELECT TOP 1000 *
FROM YourTable
ORDER BY NEWID()

--
David Portas
SQL Server MVP
--