how to query random order

[hmcheung]

How can I do this:

select * from emp order by RANDOM

??? thanks very much! Please visit my WebCam!!

http://i.am/andycheung

 

[foxdev]

You cannot do it in a single SELECT statement (unless RANDOM is the name of a column in your table, of course). You could probably create a cursor and move forward a random number of records, or if you had a unique integer column you could randomly generate a number and select the matching record. Robert Bradley

http://www.foxdev.com/

Coming Soon:

http://www.lowcostprints.com

http://www.agrainofhope.org

 

[tcorrigan]

You can do the following

select
rand() as random,
*
into
#RandomEmp
from
emp

select
*
from
#RandomEmp
order by
random

 

[tcorrigan]

Sorry, I got a little too quick on the trigger on the last response. If you actually LOOK at the output you'll see that the rand() function only fires once and that value is used in every record. The following really does work:

set nocount on

declare Random cursor for
select
empkey <-- the primary key for the emp table
from
emp

declare @empkey int

create table #RandomEmp (
random numeric(18,18),
empkey int
)

open Random
fetch next from Random into @empkey
while (@@fetch_status <> -1) begin
insert into #RandomEmp (random, empkey) values (rand(), @empkey)
fetch next from Random into @empkey
end

select
*
from
#RandomEmp r join emp e on r.empkey = e.empkey
order by
random

drop table #RandomEmp
close Random
deallocate Random