Thursday, October 11, 2007

oracle get a random value out of a query result

I have a query:

select id from class_schedule where id not in (select distinct(event_id) from trainer_schedule)



I want to pick up a random value from this query result and assign it to a varible e.g. randomID.

solution:


randomID :=
select id from
(
select id from class_schedule
where id not in (select distinct(event_id) from trainer_schedule)
order by dbms_random.value
)
where rownum = 1

No comments: