We help IT Professionals succeed at work.

Select Random from a table

hotbc
hotbc asked
on
I have a table that consist of 100 record, i want to select 5 record from that table, but the result must be random record.

Evertime, i'm execute the query, i'm expected to get 5 random record from that table.


Can you help me ?


Regards,

Benny
Comment
Watch Question

Commented:
hi there,

below you ca

Commented:
hi there,

below you ca

Commented:
hi there,

below you ca
Commented:
sorry about those....

below you can find a sample program that does what you need. you need to adjust table definitions, perhaps rand. generator and for avoiding duplicate random recs the key-check in the dynamic sql. (... where i not in ... ).

Cheers

set nocount on
create table randd(i int)          -- demo table
go

-- populate demo table
declare @i int
set @i = 1
while @i <= 100
begin
     insert into randd select @i
     set @i = @i + 1
end
go

create table rand5(i int)
go

-- select random recs
declare @sql varchar(2000)     -- dynsql buffer
declare @i int          -- loop control variable
declare @r int          -- random no.
set @i = 0
while @i < 5          -- set for the number of random recs
begin
     -- random no. generation (sample)
     set @r = (select rand( (datepart(mm, getdate()) * 1000000 ) + (datepart(ss, getdate()) * 10000 ) + datepart(ms, getdate())*1000 ))*(select count(*) from randd) -- make it no bigger than the no. of recs in table
     -- dynsql selection of recs
     set @sql = 'select top ' + cast(@r as varchar) + ' * into #randd from randd where i not in (select i from rand5); insert into rand5 select top 1 * from #randd order by i desc'
     exec (@sql)
     set @i = @i + 1
end
go

-- get resulting 5 random recs
select * from rand5
go

-- clean up
drop table rand5
drop table randd
go

Author

Commented:
i will try it first ... wait for me ok :)

Author

Commented:
Thanks George. ^_^

Regards,

Benny

Commented:
thanks Benny! :)
Cheers

Explore More ContentExplore courses, solutions, and other research materials related to this topic.