Solved

How can I retrive random records ?

Posted on 2000-05-15
15
462 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
In T-SQL cursor convert smallint to varchar 15 51
SQL Query (lookup) 8 61
get count of orders by customer Sql Server table. 3 46
Section based report in SSRS 14 27
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

739 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