Thanks for explaining. I haven't had a need to work with Rand function
before so I didn't know that.


Brian Freeman
Carnegie Technologies/Bluewave Computing
(770) 916-0595 ext. 415
www.carnegie.com
www.bluewave-computing.com


-----Original Message-----
From: Franz Zoister
[mailto:mssqldba-ezmlmshield-x96761124.[Email address protected]
Sent: Friday, February 25, 2005 3:24 PM
To: LazyDBA Discussion
Subject: RE: Query Help - Random records

Hi!

The reason why I suggested a cursor is that if you do
select K, rand()
from tableA
you get the same random number in each row, the rand() is only executed
once for the select statement and not for every single row.

regards FZ


-----Original Message-----
From: Brian Freeman
[mailto:mssqldba-ezmlmshield-x31635298.[Email address protected]
Sent: Freitag, 25. Februar 2005 13:33
To: LazyDBA Discussion
Subject: RE: Query Help - Random records


This is a good idea, although you do not need a cursor, just do a select
into the table as a whole.
After the table is created issue:
insert into #B (
K,
RandomNumber
)
select
@keyValue,
rand()
from tableA

Brian Freeman
Carnegie Technologies/Bluewave Computing
(770) 916-0595 ext. 415
www.carnegie.com
www.bluewave-computing.com


-----Original Message-----
From: Franz Zoister
[mailto:mssqldba-ezmlmshield-x56442687.[Email address protected]
Sent: Thursday, February 24, 2005 11:37 AM
To: LazyDBA Discussion
Subject: RE: Query Help - Random records

A way could be the following:

you have the source table looking like this
table A
(
K <keytype> primary key,
other columns...
)


you create a temporary
table #B
(
K <keytype> not null,
RandomNumber float not null
)


now use a cursor for looping through every record in table A and do:
begin
fetch next from cursorEveryRowFromTableA into @keyValue
-- key column of current record in cursor


insert into #B (
K,
RandomNumber
)
select
@keyValue,
rand()
end


then you create an index on #B (RandomNumber)

then you do:
select top <(number of total Rows) * 0.2> K
from #B
order by RandomNumber

which is delivering keys randomly selected.


Maybe there are more elegant ways to do it but this one should work.


regards FZ


-----Original Message-----
From: Mindy Riddick
[mailto:mssqldba-ezmlmshield-x33149489.[Email address protected]
Sent: Donnerstag, 24. Februar 2005 17:02
To: LazyDBA Discussion
Subject: Query Help - Random records


Hi everyone,

I need to create a query to randomly select 20% of the records in my
database. I am familiar with randomly creating numbers using RAND() and
am researching possible solutions. If anyone has any suggestions, I
would greatly appreciate it.

Mindy


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]




MS Sql Server LazyDBA home page