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
Received on Fri Jan 12 2001 - 15:53:14 CST
