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