RE: Random data retrieval

From: Sachin Puri <sachin_at_siworldwide.com>
Date: Fri, 12 Jan 2001 22:06:58 +0530
Message-Id: <10739.126515@fatcity.com>

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-----
From: Richard Ji [mailto:richard_at_letsplay.com] Sent: Friday, January 12, 2001 7:57 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Random data retrieval

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
--------------------------------------------------------------------