RE: Random data retrieval

Deshpande is exactly right about the sample clause.The sample clause along with
rownum can be used to retrieve a random row however sample function is not allowed
in a statement with multiple table references so it won't work in my case. Hence in my case desired result can't be achieved by using the same in a single query.
However i have still used the sample function along with rownum to achieve the result
by using it in a pl/sql block.

Now consider if there are more than one users with the minimum bid rate and u want to have
only a single random user.In this case I inserted the all the users with minimum bid rate
into a single column table (dynamically created) and then used the sample clause along with rownum and
fired the query against the dynamic table to achieve the result.The dynamic table is automatically dropped as the session is over.

 thanks all the dbas .

Rgds,
Sachin Puri
Oracle Development and Administration
Solutions Infosystems

-----Original Message-----
From: Deshpande, Kirti [mailto:kirti.deshpande_at_verizon.com] Sent: Saturday, January 13, 2001 3:26 AM To: Multiple recipients of list ORACLE-L Subject: RE: Random data retrieval

Very Good. I did not think about the ROWNUM. However, he was trying to do 2 things in one SQL statement. One, getting min bidrate and second, trying to get just one random row for the min bidrate. SAMPLE function in this case (one SQL stmt, that is) will see more than one table involved (even it is the same) and will complain. Unless these 2 activities are split (probably with a PL/SQL block), I am not sure how SAMPLE can be used. Any other ideas are welcome, since I am also interested in finding out a good solution to this problem. If a PL/SQL code is the only solution, he already has it.
Cheers !
- Kirti Deshpande

> -----Original Message-----
> From: Arvind Aggarwal [SMTP:oradba9_at_yahoo.com]
> Sent: Friday, January 12, 2001 3:12 PM
> To: Multiple recipients of list ORACLE-L
> Subject: 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 .
>
> 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 retreive only one row from
> the random sample.
>
> Thanks,
>
> Arvind Aggarwal
>
> --- Kevin Kostyszyn <kevin_at_dulcian.com> wrote:
> > Wouldn't that just return the first row in the
> > table?
> >
> > -----Original Message-----
> > Sent: Friday, January 12, 2001 3:12 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > How about use "rownum <= 1" to limit it to one row?
> >
> > -----Original Message-----
> > Kirti
> > Sent: Friday, January 12, 2001 2:34 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > 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
> > 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: Kevin Kostyszyn
> > INET: kevin_at_dulcian.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).
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Photos - Share your holiday photos online!
> http://photos.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Arvind Aggarwal
> INET: oradba9_at_yahoo.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: 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