| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Random data retrieval
Hi,
Sachin according to your requirement. I think if you mix sample with rownum clause it works. I tested it on my system and it works.
select trans_map_k from trans_map sample(2) where trans_map_k like '%1%' and rownum<2;
Everytime the result is different. By using SAMPLING+ROWNUM, it will get one row from the random sample.
Thanks,
Arvind Aggarwal
--- "Deshpande, Kirti" <kirti.deshpande_at_verizon.com>
wrote:
> Hi Sachin,
> You are better of with your solution.
> The SAMPLE function can not be used in your case, as
> it won't necessarily
> return just one row that you expect. It will return
> all qualifying rows when
> using BLOCK sampling, or it will return a number of
> qulifying rows equal to
> the PERCENTAGE value specified for the sample. And
> for just 1 row output,
> that would be pretty difficult to figure out :)
> Secondly, SAMPLE works for queries that select from
> just one table. No table
> joins are allowed.
> HTH.
> - Kirti Deshpande
>
> > -----Original Message-----
> > From: Sachin Puri [SMTP:sachin_at_siworldwide.com]
> > Sent: Friday, January 12, 2001 10:37 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Random data retrieval
> >
> >
> > i am using Oracle 8i only .
> > I know that command (i.e sample) however not sure
> how to use this command.
> > Can u tell me how to use this in the query.
> >
> > Thanks in advance.
> >
> > Sachin Puri
> >
> > -----Original Message-----
> > Sent: Friday, January 12, 2001 7:57 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > If you are using 8i you can use the "sample"
> clause of the select
> > statement.
> >
> > -----Original Message-----
> > Sent: Friday, January 12, 2001 4:36 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > Consider a table A :
> > LesseeID BidRate
> > 100 2.4
> > 101 2.5
> > 102 3.5
> > 103 2.4
> > 104 2.4
> >
> > Now I want to retrieve LesseeId with minimum
> BidRate however please note
> > that I just need a single LesseeID and that too
> randomly.
> > Now if I use :
> > select LesseeID from A where BidRate = (select
> min(BidRate) from A);
> > The output is :
> > LesseeID
> > ---------
> > 100
> > 103
> > 104
> >
> > My Question:
> > Does anyone knows how to write a query or any
> pl/sql block in order to
> > retrieve only one LesseeID and that too randomly
> so i can get only
> > a single LesseID and that single LesseeID should
> be changing each time i
> > use the query or block .
> > Please note that I don't want to use 'dbms_random'
> .
> > I have actually written a pl/sql block that works
> perfectly fine however i
> > want to have it done in a single query or may be
> by using small piece of
> > code.
> >
> >
> > Rgds,
> > Sachin Puri
> > Oracle Development and Administration
> > Solutions Infosystems,
> >
> >
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
 |
 |