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
Received on Fri Jan 12 2001 - 13:32:29 CST
