Solved

How can I retrive random records ?

Posted on 2000-05-15
15
424 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 142

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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now