How do I NTH Select in SQL????

[ja]

Hello,

I have a table that contains approx 2 million records. I want to
query 2,000 random records.

Example: 500,000 names divided by 20,000 samples = 25. The N is 25,
meaning samples would be sent to every 25th name on the list.

this is what I am using to create a random selection

order by substing(f1,4,1) + substing(f2,3,1) + substing(f3,3,1) +
substing(f4,2,1)

This method takes a very long time.

Any suggestions????
TIA
ja

[KenJ]

Hi ja,

To randomize your result set, you can order by newID(), like this...
SELECT TOP 25 * FROM sysdatabases
ORDER BY newID()

[KenJ]

Hi ja,

To randomize your result set, you can order by newID(), like this...
SELECT TOP 25 * FROM sysdatabases
ORDER BY newID()

[Ellen K]

If your table has a numeric identifier, use the modulus (%) operator.

Using your example of 500,000 rows divided by 20,000 samples = 25, use
WHERE Identifier % 25 = 0
..

HTH. :)

[Ellen K]

If your table has a numeric identifier, use the modulus (%) operator.

Using your example of 500,000 rows divided by 20,000 samples = 25, use
WHERE Identifier % 25 = 0
..

HTH. :)

[DickChristoph]

Hi Ja

Well First lets create some data. I'll create a table of Social Security
Numbers (SSN) and Names (Name 1, Name 2, etc).

Create Table People(
SSN varchar(11) not null Primary Key,
Name varchar(50))

declare @counter int
declare @f1 int
declare @f2 int
declare @f3 int
declare @c int
declare @SSN varchar(11)

set nocount on

set @Counter = 1
while @Counter <= 2000000
begin
set @F1 = 100 + cast((rand() * 900) as int)
set @F2 = cast((rand() * 100) as int)
set @F3 = cast((rand() * 10000) as int)
set @SSN = dbo.ZeroFill(@f1, 3) + '-' + dbo.ZeroFill(@f2, 2) + '-' +
dbo.ZeroFill(@f3, 4)
set @C = (Select count(*) from People where SSN = @SSN)
if @C = 0
begin
insert People(SSN, Name) values(@SSN, 'Name ' + cast(@counter as
varchar(7)))
Set @Counter = @Counter + 1
end
end

--Run Script to Here to Create DATA

Create Function ZeroFill(@num as int, @digits as int)
returns Varchar(4)
as
begin
declare @Tmp as varchar(4)
declare @n as int
set @tmp = cast(@num as varchar(4))
while len(@tmp) < @digits
begin
Set @tmp = '0' + @tmp
end
return @tmp
end
---
The Proc SelectSamplePeople will return a random list of People from the
People table.

Create Proc SelectSamplePeople(@SampleSize int)
as

set nocount on

Create table #TempPeople(
RowID int not null identity(1,1) Primary Key,
SSN varchar(11) not null)

--Order by NewId() to get People in Random Order
declare @SQL varchar(2000)
set @SQL = 'Insert #TempPeople(SSN) Select top ' + cast(@SampleSize * 25 as
varchar(12)) + ' SSN from People order by NewID()'

Exec(@SQL)

--Use t.RowID % 25 = 0 to get the 25th person from the Random List

Select p.SSN, p.Name
from People p
inner Join #TempPeople t
on p.SSN = t.SSN
where t.RowID % 25 = 0
--End of Procedure

So Now

SelectSamplePeople 20000

Will get 20000 Random People from the list.
If you forget about the every 25th Name idea, this next proc will get a
random Sample of the same size and it runs somewhat faster and uses less
temporary space.

Create Proc SelectSamplePeople1(@SampleSize int)
as

set nocount on

declare @SQL varchar(2000)
set @SQL = 'Select top ' + cast(@SampleSize as varchar(12)) + ' SSN, Name
from People order by NewID()'

Exec(@SQL)

--End of Proc

SelectSamplePeople1 20000

Will get 20000 Random People from the list

--
-Dick Christoph
"ja" <jo******@sbcglobal.net> wrote in message
news:sj********************************@4ax.com...

Hello,

I have a table that contains approx 2 million records. I want to
query 2,000 random records.

Example: 500,000 names divided by 20,000 samples = 25. The N is 25,
meaning samples would be sent to every 25th name on the list.

this is what I am using to create a random selection

order by substing(f1,4,1) + substing(f2,3,1) + substing(f3,3,1) +
substing(f4,2,1)

This method takes a very long time.

Any suggestions????
TIA
ja

[--CELKO--]

Do this in SAS or SPSS or whatever your statistical package is. They
usually have a well-defined sampling routine that will not have
problems with the distribution of the sample data.

SQL is not always the answer.

[Alexander Kuznetsov]

yet another approach is to use rand():

create view wrapped_rand_view
as
select rand( ) as random_value
go
create function wrapped_rand()
returns float
as
begin
declare @f float
set @f = (select random_value from wrapped_rand_view)
return @f
end

select c.customer, dbo.wrapped_rand() wrapped_rand
into #t
from customers c

select top 1 percent c.* from customers c join #t on
c.customer=#t.customer
order by wrapped_rand