August 17, 2003 at 4:26 pm
Dear SQL Gurus;
I am trying to select a certain (say 4 records) random number of
records from a record set. Here is my select statement. But for some reason
It returns the same 4 records every time I fire the script. I would like to find a
sample stored procedure. Is there a way to first select certain number of records from
a table and insert into a temp table and then select 4 random records…
Thanks so much for your help….
Here is my sample select statement…(It returns same 4 products over and over again. I wanted get 4 different records every time I fire this script…)
set rowcount 4
select b.idProduct, b.chrProductName, c.chrSubDeptName from
SubDepartmentProducts a, Products b, SubDepartment c, department d, DeptSubDept e
Where a.idProduct = b.IdProduct and
c.idSubDepartment = a.idSubDepartment and
d.chrDeptName = "Electronics" and
c.chrSubDeptName ="Computers" and
a.idSubDepartment = e.idSubDepartment and
e.idDepartment = d.idDepartment and
b.idProduct >=(rand()* (select max(idProduct) from Products)) and
a.idProduct <> 474 and (I am sendind this id through a from..)
b.chrProductName like '%' + "Dell"+ '%'
order by b.chrProductName
Set rowcount 0
August 17, 2003 at 5:29 pm
I sometimes use NEWID() to "randomise" results. Eg.
select top 4 * from pubs.dbo.authors
order by newid()
Cheers,
- Mark
Cheers,
- Mark
August 17, 2003 at 9:39 pm
I'll rather go with mccork's suggestion.
select SeqNum=Identity(INT,1,1),
b.idProduct,b.chrProductName,c.chrSubDeptName
INTO #TMP from
August 17, 2003 at 9:47 pm
Read BOL 2000: Using RAND
Execute the following
Select f.a,Rand(f.a),rand()
from (Select 0 union Select 1 union Select 2) as f(a)
August 18, 2003 at 10:52 am
Hi...
I just would like to thank to those who replied my question..
newid() works perfect !!
NG
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply