Re: rownum, aggregates and (inline) views...

And what version are you working on?
In case you did not look, its being discussed that the problem probably exists in 8.1.6 and not in the higher versions.

If your 8.1.6 gives different results .. then I guess I'm losing it :)

BTW: O/S is Solaris (which is same in OP and my case)

"Mahesh" <HalpethM_at_Logica.com> wrote in message news:1030097735.68683_at_ernani.logica.co.uk...
> Hey Guys !!
>
> Interestingly, I don't think there is any problem with the original queries
> being discussed ...
>
> select * from emp
> /
>
> EMPNO
> ----------
> 1
> 2
> 3
> 4
>
> 4 rows selected.
>
> select *
> from (
> select *
> from (
> select *
> from (
> select *
> from (select distinct empno
> from emp)
> where empno = 3
> )
> where rownum < 2
> )
> )
> /
>
> EMPNO
> ----------
> 3
>
> 1 row selected.
>
>
> I tried moving the "where rownum < 2" to a level below... still it works
> fine... wots the prob ???
>
> -Mahesh.
>
>
> "Anurag Varma" <avdbi_at_hotmail.com> wrote in message
> news:umbbvn51i6qva1_at_corp.supernews.com...
> > Quite true. Saw this strange behavior in 8.1.6.3
> >
> > More tests:
> > 1 select r, id from (
> > 2 select rownum r, id from (
> > 3 select id from (select distinct t.id from t)
> > 4 where id=4
> > 5 ) where rownum < 4
> > 6* )
> > SQL> /
> >
> > R ID
> > ---------- ----------
> > 1 4
> >
> > 1 select r, id from (
> > 2 select rownum r, id from (
> > 3 select id from (select distinct t.id from t)
> > 4 -- where id=4
> > 5 ) where rownum < 4
> > 6* )
> > SQL> /
> >
> > R ID
> > ---------- ----------
> > 1 1
> > 2 2
> > 3 3
> >
> > If its merging these two queries then the above results are just
> contradictory.
> > I just cannot explain how oracle is processing them?
> >
> > Anurag
> >
> >
> > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3d650f41$0$237$ed9e5944_at_reading.news.pipex.net...
> > > It does seem a bit odd. FWIW your test does NOT reproduce on 8.1.7 or
> 9.2 on
> > > Win2000. Perhaps an upgrade would resolve the issue.
> > >
> > >
> > > --
> > > Niall Litchfield
> > > Oracle DBA
> > > Audit Commission UK
> > > *****************************************
> > > Please include version and platform
> > > and SQL where applicable
> > > It makes life easier and increases the
> > > likelihood of a good answer
> > >
> > > ******************************************
> > > "Gui" <koen.janssens_at_alcatel.be> wrote in message
> > > news:11ecf2b.0208220805.29a6e51b_at_posting.google.com...
> > > > Hello all.
> > > >
> > > > I am experiencing some weird query behavior trying to get the to N
> > > > rows of a view that contains a 'distinct' select. Although the real
> > > > life query is a bit more complex, i found a simple sample query that
> > > > has the same problem. I know this query is not very usefull an not
> > > > optimal, but its just to make a point:
> > > >
> > > > I have one table containing one integer field. The table contains 4
> > > > rows:
> > > >
> > > > create table t (id numeric);
> > > > insert into t values (1);
> > > > insert into t values (2);
> > > > insert into t values (3);
> > > > insert into t values (4);
> > > >
> > > > Then i do a simple select:
> > > >
> > > > select * from (select distinct id from t) where id=3
> > > >
> > > > This returns '3' as expected. Note that the 'select distinct id from
> > > > t' is a nested query.
> > > >
> > > > Now i only want to retrieve the first row of the result set of the
> > > > previous query:
> > > >
> > > > select * from (
> > > > select * from (select distinct id from t) where id=3
> > > > ) where rownum < 2
> > > >
> > > > This still returns '3', again as expected.
> > > >
> > > > Now i wrap another select * around this query:
> > > >
> > > > select * from (
> > > > select * from (
> > > > select * from (select distinct id from t) where id=3
> > > > ) where rownum < 2
> > > > )
> > > >
> > > > For some mysterious reason, this query does not return any rows
> > > > anymore.
> > > >
> > > > The only reason i can think of it that oracle merges the two where
> > > > clauses into one (id = 3 and rownum < 2). This would explain why i
> > > > don't see any results. But i have no idea why oracle does this in the
> > > > last query but not in the second one.
> > > >
> > > > Any ideas ? Am i missing something here ?
> > > >
> > > > We are using oracle 8.1.6 solaris edition.
> > > >
> > > > PS: If i add a rownum to the select part of the query, it works again:
> > > > select * from (
> > > > select * from (
> > > > select rownum r, q.* from (select distinct id from t) q where id=3
> > > > )q where rownum < 2
> > > > )
> > > > returns '3'. Don't ask me why....
> > >
> > >
> >
> >
>
>
Received on Fri Aug 23 2002 - 07:27:52 CDT