How (in the world) is this random?
Alan Shein <alanshein_at_erols.com> wrote in message
news:809c8f$8r$1_at_autumn.news.rcn.net...
> Please read the SQL statement carefully. Note the "HAVING" clause. What
the
> statement does is first return all records where the rownum id <41, so
> rownums 1-40 are returned. But, we only want rows 20-40. The "having"
clause
> allows only those records whose rownum is >19 (20-40) to be returned,
> resulting in 21 records, rownums 20-40. Note that GROUP BY must be used
with
> HAVING.
>
> Try it, it works.
>
>
> <tedchyn_at_yahoo.com> wrote in message news:809aqv$ts9$1_at_nnrp1.deja.com...
> > alan, rownum > 19 will not work. it will give you a result of no row
> > selected. there was a discussion at this forum about why it does not
> > work sometime back.
> >
> > SQL> select count(*) from t;
> >
> > COUNT(*)
> > ---------
> > 118
> >
> > SQL> select * from t where rownum > 10 and rownum < 20;
> >
> > no rows selected
> > ted chyn
> > In article <7vvcnj$13e$1_at_autumn.news.rcn.net>,
> > "Alan Shein" <alanshein_at_erols.com> wrote:
> > > This will work:
> > >
> > > select rownum, <column>
> > > from <table>
> > > where rownum < 41
> > > having rownum > 19
> > > group by rownum, <column>
> > >
> > > Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message
> > > news:3822BD91.47C5_at_yahoo.com...
> > > > Jeff Guttadauro wrote:
> > > > >
> > > > > Hi, Connor.
> > > > >
> > > > > I'm confused... Wouldn't this return no rows when xxx >
> > the #
> > > > > of rows in table a and only 1 row when xxx <= the # of rows in
> > table
> > > > > a, since the "each_row = xxx" condition will restrict the result
> > set
> > > > > to that row having a rownum value of xxx from the subquery?
> > > > >
> > > > > Maybe the query is missing something... Or maybe I'm
> > missing
> > > > > something...
> > > > >
> > > > > -Jeff
> > > > >
> > > > > On Thu, 04 Nov 1999 19:10:52 +0800, Connor McDonald
> > > > > <connor_mcdonald_at_yahoo.com> wrote:
> > > > >
> > > > > >kev wrote:
> > > > > >>
> > > > > >> Hi,
> > > > > >>
> > > > > >> How do I select, say, 10 random rows from a dataset?
> > > > > >>
> > > > > >> - Kev
> > > > > >
> > > > > >(if your table is not too large...)
> > > > > >
> > > > > >select *
> > > > > >from ( select rownum each_row, a.*
> > > > > > from table a )
> > > > > >where each_row = xxx
> > > > > >and rownum < 10
> > > > > >
> > > > > >where 'xxx' is whatever appropriate random number generator you
> > want
> > > > > >(eg dbms_random)
> > > > > >--
> > > > > >===========================================
> > > > > >Connor McDonald
> > > > > >"These views mine, no-one elses etc etc"
> > > > > >connor_mcdonald_at_yahoo.com
> > > > > >
> > > > > >"Some days you're the pigeon, and some days you're the statue."
> > > >
> > > > Sorry - typo on my part...the following works...
> > > >
> > > > SQL> create table test1 ( x number );
> > > >
> > > > Table created.
> > > >
> > > > SQL> insert into test1 select rownum from all_objects
> > > > 2 where rownum < 999;
> > > >
> > > > 998 rows created.
> > > >
> > > > SQL> commit;
> > > >
> > > > Commit complete.
> > > >
> > > > SQL> select *
> > > > 2 from ( select rownum xxx, a.* from test1 a)
> > > > 3 where xxx in ( select random.rndint(500)
> > > > 4 from test1
> > > > 5 where rownum < 10 )
> > > > 6 /
> > > >
> > > > XXX X
> > > > --------- ---------
> > > > 15 15
> > > > 69 69
> > > > 133 133
> > > > 160 160
> > > > 167 167
> > > > 221 221
> > > > 338 338
> > > > 350 350
> > > > 450 450
> > > >
> > > > 9 rows selected.
> > > >
> > > > SQL>
> > > > --
> > > > ===========================================
> > > > Connor McDonald
> > > > "These views mine, no-one elses etc etc"
> > > > connor_mcdonald_at_yahoo.com
> > > >
> > > > "Some days you're the pigeon, and some days you're the statue."
> > >
> > >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
Received on Wed Nov 10 1999 - 17:17:09 CST
