Re: Joining on rownum problem

Hi
You got only a tuple because of your global variable a which refers to the outside table Test. The sub-query is computed with theses values of the first tuple and the sub-query is then replaced by the result which contains
1 tuple. Finally, the first level query is computed using this one tuple table. It can gice only a 1 tuple answer.

André

Jim Kennedy wrote:

> rownum is just the number of the row that comes back from the result set
> before any sorting. It is not the actual number of the row in the database.
> So the following would not return any rows:
> select * from test where rownum>2;
>
> Why? Because rownum is the number of the row in the result set and in the
> above query you are limiting the result set by itself. Rownum is arbitrary.
> Jim
>
> "Mike Moore" <hicamel_at_mail.home.com> wrote in message
> news:4ZFz6.1084$ix4.657070_at_news1.rdc1.sfba.home.com...
> > 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?
> > thanks
> > Mike
> >
> >
> >
> >
> >
  Received on Sun Apr 29 2001 - 14:08:34 CEST