rownum, aggregates and (inline) views...

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 Thu Aug 22 2002 - 11:05:46 CDT