Solved

How can I retrive random records ?

Posted on 2000-05-15
15
452 Views
Last Modified: 2012-05-04
How can I retrieve random records in a sql server 6.5 ?

IN other databases ..

'SELECT * FROM tablename ORDER BY RAND()'

will retrieve records in random everytime ..


Will the same work in an MS-SQL-SERVER 6.5 ?

if not what is the best way ?
0
Comment
Question by:christopher sagayam
15 Comments
 
LVL 6

Expert Comment

by:crsankar
ID: 2810272
The rand() function is available in sql server 6.5 also. So, the select statemnt should work there too.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2810319
I confirm that Rand() exists in SQLServer 7, but i think it sorts by a specific column, determined by the Rand() value
0
 
LVL 6

Author Comment

by:christopher sagayam
ID: 2810571
'SELECT * FROM tablename ORDER BY RAND()'

What I meant was the above statement does not retrieve random order ..It  retrieves in a fixed order .. DO we have to seed the RAND function ??
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 9

Expert Comment

by:david_levine
ID: 2810951
ORDER BY specifies a column. This will just return results sorted in ascending order by the column that gets picked in the RAND function.

I think you want to return data randomly. My past experience is that the RAND() function in set processing, always returns the same number for all rows, so if you wanted to create a column that contained your random sort sequence number, they'd all be the same value. I'm not sure if that's still true since I haven't tried it in SQL Server 7.

David
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2811155
You can use a seed with the Rand function, for instance:

DECLARE @seedstr varchar(30), @seed int, @rand_number float
SELECT @seedstr = CONVERT(varchar(30), GetDate(), 114)
SELECT @seed = CAST(RIGHT(@seedstr, 3) AS int) * CAST(SUBSTRING(@seedstr, 7, 2) AS int)
SELECT @rand_number = RAND(@seed)
0
 

Expert Comment

by:allenstoner
ID: 2811610
 I've used the RAND function on sets of records in SQL 7 and from my experience you'll get the same value for each row.  The only way I know to really do this is ugly and that is to use a cursor or something to loop through the records updating a field to a random number.  Then do the select.  Not terriably effecient.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2813505
You can do an ORDER BY SomeColumn % @random_value

('%' is the modulo operator). Make sure that the SomeColumn contains a lot of different values (preferably a unique numerical column) and that the @random_value is truly random and an integer somewhere between 0 and about .05 * MAX(SomeColumn). Use the code I gave above to get a good starter, and then use something like this:

DECLARE @random_value int
SELECT @random_value = (((@rand_number * 100) % 20) / 100) * MAX(SomeColumn) FROM YourTable

to get the random value between 0 and .05 * MAX(SomeColumn)

This will give you a fairly different sorted set each time. Not truly random because of the modulo operation, but random enough to fool any human being. You can experiment with different values for the '% 20' operation above to increase or decrease randomness (experiment with values from 1 to 99). The best thing would be to take a random value here too, but that might be a bit over the top.
0
 
LVL 6

Author Comment

by:christopher sagayam
ID: 2813847
Jeremy D

Im using Ms-SQL server 6.5

CAST was not working

and % ( modulo) was NOT working using a float type ..

So I used the following SQl statements .

I do get a random arrangement of results ..

But as you said NOT truly random ..

Results get repeated often ..


DECLARE @rand_val float

SELECT @rand_val = RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )

DECLARE @temp int

SELECT @temp = (@rand_val * 100)

SELECT * FROM table1 ORDER BY @temp % TESTCOL


Is there anyway to make it more random ??

0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2816198
>> CAST was not working
In 6.5, use CONVERT, same result.

>> and % ( modulo) was NOT working using a float type ..
Sorry, you have to convert it to an int first, forgot.

What you're doing now is @random_value modulo TESTCOL. Assuming that TESTCOL holds unique values, my gut feeling says that TESTCOL modulo @random_value is going to give better results, but that's just a gut feeling.

I would still try to use my previous example if I were you though. Here it is (improved) for 6.5:

DECLARE @seedstr varchar(30), @seed int, @rand_number int, @rand_value int, @rand_moderator int
SELECT @seedstr = CONVERT(varchar(30), GetDate(), 114)
SELECT @seed = CONVERT(int, RIGHT(@seedstr, 3)) * CONVERT(int, SUBSTRING(@seedstr, 7, 2))
SELECT @rand_number = CONVERT(int, RAND(@seed) * 100)
SELECT @rand_moderator = CONVERT(int, 25 + (RAND() * 50))
SELECT @rand_value = ((@rand_number % @rand_moderator) / 100) * MAX(TESTCOL) FROM table1
/* Now for the actual select statement */
SELECT * FROM table1 ORDER BY TESTCOL % @rand_value
/* The next example will be even more random, but much slower on large tables */
SELECT * FROM table1 ORDER BY TESTCOL % @rand_value, TESTCOL % @rand_number







0
 
LVL 4

Accepted Solution

by:
Jeremy_D earned 25 total points
ID: 2816226
Sorry, missed another convert (and screwd up on implicit float to int conversion, it's early :).

This:

SELECT @rand_value = ((@rand_number % @rand_moderator) / 100) * MAX(TESTCOL) FROM table1

should be this:

SELECT @rand_value = CONVERT(int, ((@rand_number % @rand_moderator) / 100.0) * MAX(TESTCOL)) FROM table1

0
 
LVL 6

Author Comment

by:christopher sagayam
ID: 2816371
Thanks for your effort
0
 
LVL 6

Author Comment

by:christopher sagayam
ID: 2816377
Btw can you email me at christopher_sagx@yahoo.com regarding

"Interactive Intelligence Certification for Enterprise Interaction Center Basic Handler Development"

I want to know more about that ? what it is ?
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2816791
You're welcome.

It's in your mailbox.
0
 

Expert Comment

by:megablast
ID: 3007634
Hi Jeremy, really good answer.. been trying to work this out for ages.
Cheers, Neil
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 3008216
Glad I could be of help.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question