RE: Random data retrieval

How about use "rownum <= 1" to limit it to one row?

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Deshpande, Kirti
Sent: Friday, January 12, 2001 2:34 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Random data retrieval

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