| Subject | Re: is it possible? |
|---|---|
| Author | Adam |
| Post date | 2005-02-11T21:59:28Z |
You can do it, but it may be a bit tricky. I don't have time to
write the code, but this pseudo code get you working.
1. Create a UDF function that returns a random number (for example
FB_RandU(0,100) might return a uniform distribution random number
between 0 and 100).
2. In the select of your query
select id, fb_RandU(0,100)
from tablea
order by 2
This query will return both the ID and the randomised number that
was used to do the ordering.
3. Optional: If you don't want the order column in your results,
create a stored procedure wrapper around the query
create procedure randomtablea
returns
(
ID integer
)
as
begin
for select id, fb_RandU(0,100)
from tablea
order by 2
into :id
do
begin
suspend;
end
end
Then you could do
select *
from randomtablea
and every call would return you a result in a different order
(providing you get the udf code right)
Note:
This is only to prove you can actually do it. I would personally
have left randomising order to the client, but I suppose there are
situations where you may not trust the client to do anything truly
randomly. Just be aware that if you go for option 3, and run
something like
select *
from randomtablea
where id < 50
It will be very inefficient, because the procedure has to evaluate
every ID. If that is the sort of thing you are doing, then pass in
some input parameters and use them in a where clause.
Adam
--- In firebird-support@yahoogroups.com, "Gediminas" <gugini@f...>
wrote:
write the code, but this pseudo code get you working.
1. Create a UDF function that returns a random number (for example
FB_RandU(0,100) might return a uniform distribution random number
between 0 and 100).
2. In the select of your query
select id, fb_RandU(0,100)
from tablea
order by 2
This query will return both the ID and the randomised number that
was used to do the ordering.
3. Optional: If you don't want the order column in your results,
create a stored procedure wrapper around the query
create procedure randomtablea
returns
(
ID integer
)
as
begin
for select id, fb_RandU(0,100)
from tablea
order by 2
into :id
do
begin
suspend;
end
end
Then you could do
select *
from randomtablea
and every call would return you a result in a different order
(providing you get the udf code right)
Note:
This is only to prove you can actually do it. I would personally
have left randomising order to the client, but I suppose there are
situations where you may not trust the client to do anything truly
randomly. Just be aware that if you go for option 3, and run
something like
select *
from randomtablea
where id < 50
It will be very inefficient, because the procedure has to evaluate
every ID. If that is the sort of thing you are doing, then pass in
some input parameters and use them in a where clause.
Adam
--- In firebird-support@yahoogroups.com, "Gediminas" <gugini@f...>
wrote:
><helebor@t...>
>
> suppose there is the initial set:
> 1 2 3 4 5 6 7 8 9 10 11 12
> first call:
> 1 10 4 9 8 11 2 6 12 5 7 3
> second call:
> 6 9 10 3 1 7 11 2 4 8 12 5
> and so on
>
> --- In firebird-support@yahoogroups.com, Helen Borrie
> wrote:function,
> > At 08:25 PM 6/02/2005 +0000, you wrote:
> >
> > >using SP, UDF or other FB 1.5 features need to implement
> > >which randomly orders dataset (get from the select statement)using
> > >iterative algorithm (actually, every function call reorderselement
> > >position in the set).STL.
> > >As I know, the FB doesn't have set or list containers as in the
> > >Is it possible to use table as a temporary container, but ifseveral
> > >concurent calls to such function would occur?what the
> >
> > This isn't clear at all. How about a simple example showing
> > alternative sets should look like.