Random Row Selection 
Author Message
 Random Row Selection

Does anyone know of a technique to select a random sample of rows from a SQL
Server table?

Access SQL uses the following syntax:

'    '--- GET RANDOM SAMPLE FROM CURRENT RESULT SET
'    strSQL = "INSERT INTO ztRandomSample "
'    strSQL = strSQL & "SELECT TOP " & intHowManyNames & " * "
'    strSQL = strSQL & "FROM ztRespondents "
'    strSQL = strSQL & "ORDER BY GetRandom(ztRespondents.[Respondent_ID]);"
'

Is there an equivalent to this technique for SQL Server?

Any suggestions will be greatly appreciated.

Thanks,

Joe Somma
Imperium Solutions, Westport, CT



Tue, 28 Mar 2000 03:00:00 GMT
 Random Row Selection

I've only had to do this a couple times before, and you can't always get
away with this, but a really quick (and dirty) solution is:

Add a column (RANDCOL) to your table and fill it with random numbers (using
the RAND function), try to get a range of random numbers about 10 times the
number of rows in your table to cut down on dupes.

Then query out as many as you want with an order by RANDCOL.

A hidden advantage here is that once you 'seed' your table you can
duplicate your 'random' query - and you can 're-seed' your table any time
you want.

-Scott T. Jurkouich
Brickhouse Data Systems, Inc.



Quote:

> Does anyone know of a technique to select a random sample of rows from a
SQL
> Server table?

> Access SQL uses the following syntax:

> '    '--- GET RANDOM SAMPLE FROM CURRENT RESULT SET
> '    strSQL = "INSERT INTO ztRandomSample "
> '    strSQL = strSQL & "SELECT TOP " & intHowManyNames & " * "
> '    strSQL = strSQL & "FROM ztRespondents "
> '    strSQL = strSQL & "ORDER BY

GetRandom(ztRespondents.[Respondent_ID]);"
Quote:
> '

> Is there an equivalent to this technique for SQL Server?

> Any suggestions will be greatly appreciated.

> Thanks,

> Joe Somma
> Imperium Solutions, Westport, CT




Tue, 28 Mar 2000 03:00:00 GMT
 Random Row Selection

No not really so I tried to knock something up for you here goes :

Im afraid its a bit messy though - sorry.

/* Here is the test table and data */

drop table table1
go
create table table1 (
data3 int
)

insert into table1 values (101)
insert into table1 values (102)
insert into table1 values (103)
insert into table1 values (104)
insert into table1 values (105)
insert into table1 values (106)
insert into table1 values (107)
insert into table1 values (108)
go

/* Need to create the global temp table first so that the proc compiles */

select "hello" hello,1 T1 into ##testtable1 from table1
go

/* Compile the store proc */
drop proc sample
go











go

/* It needs to fail the first time - dont ask me why must part compile it*/
exec sample 2,"table1"

/* Then drop the table so there are no duplicate objects */
drop table ##testtable1
go

/*  This will mimic the calling application */


4,'table1'"+char(34)

go


2,'table1'"+char(34)

I hope this is of use to you

--
Steve Robinson MCP (SQL /NT)




Tue, 28 Mar 2000 03:00:00 GMT
 Random Row Selection

Heres how to get it with rowcount:

There are better ways to do it if you have a numeric key without gaps but
this will work with everything

drop table #temp
go
create table #temp
(name varchar(30))
go
insert into #temp
select name from master..sysobjects where type = "S"
go
set rowcount 1
go
select a.name
  from #temp a
order by (select rand()
            from #temp b
           where a.name = b.name)
go
set rowcount 0
go

Quote:

>Does anyone know of a technique to select a random sample of rows from a
SQL
>Server table?

>Access SQL uses the following syntax:

>'    '--- GET RANDOM SAMPLE FROM CURRENT RESULT SET
>'    strSQL = "INSERT INTO ztRandomSample "
>'    strSQL = strSQL & "SELECT TOP " & intHowManyNames & " * "
>'    strSQL = strSQL & "FROM ztRespondents "
>'    strSQL = strSQL & "ORDER BY GetRandom(ztRespondents.[Respondent_ID]);"
>'

>Is there an equivalent to this technique for SQL Server?

>Any suggestions will be greatly appreciated.

>Thanks,

>Joe Somma
>Imperium Solutions, Westport, CT




Tue, 28 Mar 2000 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Random row selection

2. Random Row Selection

3. Random selection of rows in a query

4. Random selection of resultset rows

5. Selecting 1 random row from each of n random groups

6. SQL-->random selections

7. Random selection from table

8. "Random" selection

9. Selection of Random Records

10. Random selection of numbers

11. random selection

12. Random selection from table


 
Powered by phpBB® Forum Software