RE: Random data retrieval

From: Shakeel Qureshi <msklq_at_yahoo.com>
Date: Fri, 12 Jan 2001 10:55:36 -0800 (PST)
Message-Id: <10739.126542@fatcity.com>

select xxxx from A sample(1)
where bidrate=xxx
Depending upon no of rows in table you can modify sample by prefixing decimals.

Regards,

Shakeel Qureshi
DBA (Oracle Certified)
squreshi_at_barpoint.com
--- Sachin Puri <sachin_at_siworldwide.com> wrote:
>
> 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: Sachin Puri
> INET: sachin_at_siworldwide.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Richard Ji
> INET: richard_at_letsplay.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Sachin Puri
> INET: sachin_at_siworldwide.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>