Solved

How can I retrive random records ?

Posted on 2000-05-15
15
443 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 46
MS SQL Server Management Studio, Save query 2 18
Loop to go backward 90 days 2 18
Return 0 on SQL count 24 28
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

775 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