create sql statement returning random data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

create sql statement returning random data

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2002-09-05 : 14:24:29

how do i create a sql statement to return data randomly. I used the following statement to get my data and then create a rand2 variable using

strNum = rst.RecordCount
strRand = strNum \ 200 '200 being how many records i wanted
rand2 = int(rnd*strRand) + 1

rand2 would give me varialbe. i would use that and skip that number of records to randomly get the data i needed.

This worked. However it did not use all the data. Meaning as i looped through these records. Once I hit 200 i got out. Is there a way to use sql to randomly select data???????

sql statement
select lname,fname,empid,item,pay_location,sub from emp_information_test WHERE item Not in (0845,1004,1060,2574,8008,8013,8014,8015,8016,8017,8019,8023,8645) and empid LIKE '_[123]%' order by lname

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-09-05 : 14:59:50

try something like this:

I think this only works in SQL 7.0+
SET ROWCOUNT allows you to tell SQL server how many rows you want to return. The "200" can be a variable (like @RowsToReturn)

ORDER BY NewID() Is a SIMPLE way to return random records.



SET ROWCOUNT 200
SELECT lname,fname,empid,item,pay_location,sub 
FROM emp_information_test 
WHERE item Not in (0845,1004,1060,2574,8008,8013,8014,8015,8016,8017,8019,8023,8645) 
AND empid LIKE '_[123]%'
ORDER BY NewID(), lname
SET ROWCOUNT 0

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-09-05 : 15:17:33

We've got a whole section on random records (http://www.sqlteam.com/FilterTopics.asp?TopicID=135). You might take a look at those.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources