Re: Joining on rownum problem

Mike Moore wrote:
>
> SQL> select two,one,four from test;
>
> TWO ONE FOUR
> -------------------- ---------- ----------------------------------------
> is this data
> fun more dick and jane
> two one four
> two most yowsa
> two bosco yowsa
> two zabba yowsa
>
> 6 rows selected.
>
> SQL> get junk1
> 1 select a.two, a.one, a.four from test a
> 2* where rownum = (select max(rownum) from test b where a.two = b.two)
> SQL> /
>
> TWO ONE FOUR
> -------------------- ---------- ----------------------------------------
> is this data
>
> I expected ....
>
> is this data
> fun more dick and jane
> two zabba yowsa
>
> This technique seems to get what I want if I use another column of the
> table instead of rownum.
> Why does it stop prematurely?

It should work the way you expect if you use rowid instead of rownum. Why? rowid is a meta-data column that doesn't depend on your query, while Rownum has no independent value outside of your query. Change the query and you change the rownum; qualifying on rownum in a WHERE clause will almost always lead to strange results (except for the common "WHERE rownum < n") because the rownum is not calculated until *after* the selection.

For instance, consider "SELECT * FROM foo WHERE rownum = n" It will bring back exactly one row when n=1 and zero rows when n != 1. It can't ever bring back more than one row, and if it returns one row, the rownum has to be one.

-Walt Received on Wed Apr 11 2001 - 16:58:35 CEST