?
Solved

How can I retrive random records ?

Posted on 2000-05-15
15
Medium Priority
?
478 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 100 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

771 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