Randomly selecting rows from a table

Question

Post reply

Randomly selecting rows from a table

achowe

SSCrazy

Points: 2075
More actions
March 18, 2005 at 5:05 am

#102442

Hi guys
I have a table from which I need to select a random number of records, e.g. 70 records, based on a specific set of criteria.

can anyone tell me the bets way to do this?

thanks again guys

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

...ORDER BY NEWID() is a good randomizer, though not really supported for that.

Here's an example using a cursor I picked up from SQL Server MVP Steve Kass

declare @keys table (

pk int primary key

)

declare C cursor fast_forward for

select OrderID from Northwind..Orders

declare @pk int

declare @r float

open C

fetch next from C into @pk

while @@fetch_status = 0 begin

set @r = rand()

if @r < 0.0125

insert into @keys values (@pk)

fetch next from C into @pk

end

close C

deallocate C

select * from Northwind..Orders O join @keys K

on K.pk = O.OrderID

The only disadvantage here is, that you have no real control over the number of rows inserted into the table variable.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 2

cheers frank, much appreciated.....

.....would NEWID() work for this kind of thing? do yu have any examples of this in action?

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Doesn't make too much sense now, but have a look at this and you'll get the idea:

USE PUBS

SELECT TOP 10

au_fname

, au_lname

FROM

authors

WHERE

au_fname > 'L%'

ORDER BY

NEWID()

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 4

Nice one, NEWID() seems to work fine. Do I need to pass any values into NEWID(x)?

Seems to work fine without though......

Cheers

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Try it out, and you'll see

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 6

Great! I have the following code. there is also an age field included which I want to use to split the results of the following query, i.e. 35 out of the resulting 75 in one age group and 35 in the other age group. Can I add a cluase into this code to do this all in one go?

Any ideas of the best way of doing this? is it possible to split on a certian field?

select top 75 *

into t_Female

from t_Branch

where Gender = 'Female'

order by NEWID()

go

cheers

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Hm, it would be better to explicitely create the table first and then to use two separate statements to select n rows from one group and the rest from the other group. Unfortunately you can't use a UNION or UNION ALL here, since you can have only one ORDER BY clause in such a statement. So something like this doesn't work

use pubs

go

select top 2 au_lname, au_fname, zip

from authors

where zip <= '50000'

order by newid()

union all

select top 2 au_lname, au_fname, zip

from authors

where zip >= '50000'

order by newid()

However, if you remove the union all the query works just fine. And, btw, SELECT INTO can cause trouble and performance degradation so it's better to be explicite in your DDL.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

achowe SSCrazy Points: 2075 More actions · Answer 8

cheers Frank.

Still leaves me with the same problem really I think.

Table of thousands of people, male/female of two different age groups.

need a random sample of 75 male/75 female.

Of the 75 male, I need to split this 50/50 by the two age groups. Just cant see how I can manipiulate by original query to get this to work.

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Since each sample should be taken randomly, I assume, I think you must bite the bullet and write 4 queries SELECT ... BY NEWID(). Sorry, I don't know any other way (though that doesn't mean none exists).

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]